How to filter a list field with a different Data Type

Hi so I have 2 different data types “Applications” and “Payments”. In “Applications” one of the fields is Payments which is a list of “Payments”. In “Payments” there is a field - “Status” that can either be “Pending”, “Approved” or “Rejected”.

I want to show the count of “Applications” where the List of “Payments” has at least one payment with “Pending” or “Approved” status.

Basically, if all the “Payments” in an “Application” are of Status “Rejected”, don’t count that Application.

Hi there, @breldan… I played around with this one a bit, and if I understand your post correctly, an advanced filter such as the following looks like it does the trick.

As you probably know, though, advanced filters take place on the client side, so the process of returning all of the applications to the browser and filtering them there would likely be slow. That being said, I’m thinking you might be able to have a field on the Application data type (maybe a yes/no field) that toggles when all of the payments in that application’s payments list are rejected. With a field like that in place, it would be easy to get the count you need. I certainly haven’t worked that idea all the way through, but it does seem like a path I might try to go down if I was doing what you have described.

Hope this helps.

Best…
Mike

1 Like

Thank you Mike,

I had gone the route of having a Status field on the “Applications” that toggles to “Approved” any time all the “Payments” in the Application are approved.

Advanced filters look tempting to implement though because I’ll have fewer fields and workflows to keep track of if I go in that direction.

1 Like

If you decide to explore options outside of advanced filters, you could flip your data structure (have each “Payments” have a link to the relevant “Application”). Then you could search for Payments’s Applications and count that, where Payments is Pending or Approved. Or you could search for Applications and do a nested search for Payments within that.