Filtering a choice component dynamically

I’m building an app for sales and trying to make a function where if the sale is totally paid, it can then be marked as delivered. I currently can’t work out what is going wrong with the current set-up.

I have a if-then field ‘Finance status’ within the ‘Sales’ table that will update as ‘Paid’ if both the :

  • Customer has paid
  • Finance has been paid

If only one has been paid, it will show as ‘Partially Paid’. If neither have been paid, it will show as ‘unpaid’.

I have a seperate ‘Sale Status’ table that looks like this :

On the ‘edit’ screen of the sales table, I have a tick for if the customer has paid and a choice component pulling the options from this ‘Sale Status’ table :


and the filtering is set up like this :


The ‘delivered’ option seems to only be taking into account the customer payment, and not the finance option. For example, even if a sale has a ‘Partially Paid’ finance status, the ‘Delivered’ option will show up.

I created this last week and it seemed to be working fine then, so I can’t work out what has gone wrong- any help very much appreciated!

This is where you lost me a little bit :slight_smile:

But anyway, from what I can see, your Sale Status/finance status could have one of three values:

  • Unpaid
  • Partially Paid
  • Paid

So if I look at your choice component filter conditions, I see:

  • Paid is empty, OR
  • Paid is Screen → Sale Status/finance status

Comparing that to the data in your Sales Status → Paid column, I would expect that “Order Taken” and “Cancelled” would always be shown (because they are empty), and “Delivered” would only be shown if the Sale Status/Finance status for the current row was “Paid”.

The thing that seems a little odd to me is that Paid column in your Sale Status column. How are the values in that column set? It’s unusual to change values associated with a choice list, so I’m not exactly sure what’s going on there…

1 Like

Thanks! I realise that my terminology isn’t ideal as the ‘finance status’ includes customer payment and ‘finance’ payment so its more confusing than necessary!!

Yes, that is exactly how I would want it to work. However for some reason the ‘Delivered’ option is shown when the ‘customer payment’ option is ticked, even when the `Sale Status/Finance status’ is ‘Partially Paid’ or ‘Unpaid’. (I have double checked that it is the right column!)

Yes- you’re right there too! The values in that column were just hardcoded. I’m certain that there would a better way to do it - if you have any general recommendations on how you would go about this kind of thing, that would be very welcome! Thank you!

I think I need a bit more context - would it be possible to show me a screen shot of your Sales table? (redact any data that isn’t relevant or shouldn’t be exposed).

Specifically, I’d like to see the columns that your Edit Form components (the checkbox and the choice component) write to - but in the larger context of the columns around them.

Sure and thanks again! There are quite a lot of columns, but these seem like the relevant ones!

The ‘Financing status’ column is a lookup from another table through a single relation.

If this is blank, it means that there is no financing, so as soon as the customer has paid, the sale should be marked as ‘Paid’.

For sales with financing, they should only be marked as paid if both the ‘Financing status’ is ‘Paid’ and the ‘Customer Paid’ is true.

The ‘Customer Paid’ is a direct input from the checkbox in the edit screen.

The ‘sale status/finance status’ column is based off the ‘Customer Paid’ and ‘Products/Financing Status’ columns.

As far as I can tell, all of these columns are working exactly as expected.

Okay, yes I think I understand now.

However, the bad news is that I can’t see any obvious reason why this shouldn’t be working the way you have it setup. But it is late here, so I might not be thinking clearly…

I might have to come back to this tomorrow and try to replicate your setup so I can take a closer look.

In the meantime, maybe somebody else might take a look and spot something that I’ve missed.

pssst… @Jeff_Hager :wink:

1 Like

Thank you so much, really appreciate it! :slight_smile:

How is the finance status column configured?
Also, is the finance relation referring to a table under row ownership?

Most likely, I would guess the finance status is not coming across due to row owners, so any time the Paid checkbox is checked and the relation is empty due to row owners, then it’s automatically flipping the status to Paid.

1 Like