Filtering Collection in Pages

[Pratik_Shah]

While filtering Collection in Pages, it gives us an option to select either OR conditions or AND conditions.

I have a requirement wherein, only those records are to be shown on screen when two conditions shall be mandatory fulfilled and any one of four conditions is fulfilled.

How do I set up this?
@Robert_Petitto @Darren_Murphy @Lucas_Pires @ThinhDinh

This is app link just in case if any one wants to see. https://compliance.glide.page/. You can submit the form in Home Page.

[Darren_Murphy]

Create an if-then-else column to use as a filter. Construct it as follows:

• if mandatory condition 1 is not true, then null
• if mandatory condition 2 is not true, then null
• if optional condition 1 is true, then true
• if optional condition 2 is true, then true
• if optional condition 3 is true, then true
• if optional condition 4 is true, then true

And your collection filter will be where if-then- else column is checked.

[Robert_Petitto]

Ya…you need to build the condition in the editor as @Darren_Murphy stated above. Wish Glide would give us more robust if conditions as proposed here:

Better and more consistent IF THEN logic

Thanks @Jeff_Hager. Yes, it’s for an IF computed column in GDE, and I am trying to simulate an “AND” by putting the “not true” conditions in the IFs and the expected result in the ELSE.

[Pratik_Shah]

Thank you for quick response @Darren_Murphy @Robert_Petitto .

Actually, I am comparing this value based on value shown on screen and these comes from form submission. So, e.x. for one Company, one is mandatory and for another, it may not be mandatory.

Let me show you the structure of database.

I have table called Submission. Submission will provide few values like, Type of Entity, Paid-up Capital, Turnover etc.

There is another table called List of Compliance (which is collection). This list is broadly bifurcated into Mandatory and Limit-based. I also have column which shows, to which type of companies a particular compliance is applicable.

If a particular compliance is “Limit-based”, then it shows an amount, if exceeds, then that particular compliance is applicable to those companies which are there in “Applies to”.

Below is my screen after form submission.

What I am doing right now?
I have added One collection with following filters (for mandatory compliance).

1. “Applies to” contains “type of company” from screen value AND
2. Type of Compliance is “Mandatory”

Real issue comes when I want to show Limit based compliance. So I have added another collection list wherein

1. Type of Compliance is “Limit-based” AND
2. “Applies to” contains “type of company” from screen value

Next filters should be like “limit of Paid-up” is less than the amount which is shown screen. OR next limit OR next limit OR next limit. So if any of the limit is fulfilled, it should show the record.

What you are saying is to add If-then-else column in Compliance list table but that won’t bring value from Submission (which varies from submission to submission)

[Darren_Murphy]

One thing is not clear to me.

Are you trying to apply this filtering within the form prior to submission, or after the form has been submitted?

If you’re trying to do it inside a form, then the method I outlined won’t work as you won’t have access to the form values. The only way to apply my method would be with a custom form that’s writing to user specific columns. Then you could use those values to construct the filtering logic.

[Pratik_Shah]

No, I am not filtering in form. I have custom form which is writing to another table but without user specific values. But still I am not able to understand how will it work because values are in a screen from different table.

Can you please guide? Thanks

[Darren_Murphy]

@Pratik_Shah

I have custom form which is writing to another table but without user specific values.

Will you ever have more than one user in that form at the same time? If yes, then you will have problems.

@Pratik_Shah

But still I am not able to understand how will it work because values are in a screen from different table.

Okay, so you need to find a way to get those values into the table that needs to be filtered. I don’t understand your setup well enough to guide you how to do that, but you’ll most likely need either a single value column or a relation + lookup. Once you have the values in that table, you can apply the logic I described earlier.

[Pratik_Shah]

Thank you for response.

@Darren_Murphy

you need to find a way to get those values into the table that needs to be filtered

I will work on it.