Filtered Value list in Portal

[jon cohen]

Hi all

I'm trying to build a simple solution which tracks which PRODUCTS have been selected on an ORDER and filters the list of available products so that for each new line on the LINE ITEMS portal, those already chosen don't appear on the list.

I have three tables: ORDERS, LINEITEMS (join table) and PRODUCTS.

My proposed solution is:

• Have an unstored  calcuation field in ORDERS called Products Chosen set to dynamically populate a List of ProductIDs as they're chosen (by using  the List function on the ProductIDs in related LINEITEMS.)

• Choose items in the LINEITEMS portal on the ORDER based on a value list called PRODUCTS UNCHOSEN which references a table occurance of PRODUCTS with a ≠ relationship to Products Chosen (from ORDERS)

First I start by using a 'normal' value list of PRODUCTS on the popup in the LINEITEMS Portal and The Products chosen field is working fine (I can watch it fill up with PRODUCT IDs as I select products on the LINE ITEMS portal.

But when I set the value list to reference the PRODUCTS UNCHOSEN table occurance I can no longer make selections at all. It says No Values Defined when I try to make selection in the popup in the LINEITEMS portal.

Have I explained this clearly enough?

I know I'm missing something, some small gotcha step but I can't figure it out. 

Thanks!

[comment]

It sounds like you have ran into the problem described here:
https://fmforums.com/topic/51074-tough-one-excluding-value-list/?do=findComment&comment=266667 

 

[jon cohen]

Thanks so much comment!

I downloaded the Dwindling file and had a look.

The architecture of the solution seems to be exactly what I've done but obviously isn't as mine isn't working!

I'll see if I can spot the gremlin and if not perhaps I could share my file so others more skilled might...

[comment]

The clue is in the following sentence from the other post:

Quote

 a field used to match a Number field in a ≠ relationship is considered empty, unless it has an actual number in it.

and an empty match field is neither equal nor not equal to anything.

If you look inside the field definition of Contacts::cMyOrganizationsIDs, you will see that it adds a dummy number (-.1) to the list so that there's always some number in the field, even when no items were selected yet.

 

Edited September 28, 2023 by comment

[jon cohen]

I did notice that -1 and wondered what it was, thanks!

How would that apply in a situation where the ID fields for Contacts and Organizations were text not numbers (ie UUIDs)?..

 

[comment]

Same way: just add any value that is not a UUID, for example:

List ( LineItems::ProductID ) & "¶a"

Even a space will do.

 

Edited September 28, 2023 by comment

[jon cohen]

Will give this a try!

It's agonizing to be SO close to having something working!

So grateful for your reply and help! 🙏

[jon cohen]

So still not having any luck here!

I added a space to the calculated products chosen (list) field hoping that this would enable the connection between that and the Products TO with the ≠ relationship.

But it still doesn't work. I also added a portal to that TO so I can monitor in real time (Products should vanish from that list as they are added to LINE ITEMS in the order.)

You can pick them from the pop up but the products still available list doesn't shrink and the value list on pop up for choosing products is not dwindling as planned.

I just can't spot what I'm doing wrong...🤯

 

TEST Dwindling Value List.fmp12

[comment]

I see two issues:

1. You are adding a space to the last value in the list, instead of adding a separate value of space. The calculation should be: 

List ( LINEITEMS::__fkPRODUCTID ) & "¶ "

2. The other issue is a matter of refreshing. If you take a closer look at my file, you will see that the drop-down field is actually a button that refreshes the window and then goes to the field (we did not have script triggers back then).

 

[jon cohen]

Corrected both of those things but still not behaving.

Please don't feel obligated but if you are so inclined to help solve the mystery, revised version attached here..

🙏

I did come across THIS which shows exactly how to do it and seems not to need extra lines on the List field or refresh scripts on the main page but again, I can't see how my file is different to this...

TEST Dwindling Value List v.02.fmp12

[comment]

There is a difference in behavior between drop-down list and pop-up menu.

To make a long story short, I modified your file to use a script trigger instead of a button. I believe it should now work as you expect. Note that you will also need to refresh when you delete an item.

TEST Dwindling Value List v.03.fmp12

Edited September 28, 2023 by comment

[jon cohen]

Thank you so much!

Will have a play.

Can't thank you enough for being so generous with your time and attention.

Fantastic!

Thank you so much. 

Needless to say this is just a practice sandbox for the actual solution I'm developing (which has nothing to do with Orders or Products but still requires a dwindling value list).

So to that end, did you do anything other than change the button to a script trigger?

 

[jon cohen]

ok! I (ALMOST) have it working in my actual solution.

But one weird this is happening:

On existing records with existing line items, I can add new line items,  the list filters beautifully and all works exactly as expected.

But on creating a new record I'm unable to add ANY line items (it says <no values defined> when I try  to click on the pop up menu to access the filtered list.)

Thoughts?

 

[comment]

4 hours ago, jon cohen said:

But on creating a new record I'm unable to add ANY line items (it says <no values defined>

That's the symptom you started with, is it not? I cannot see your file, but the most likely reason is the same one: the match field (the same calculation field that adds a space) is empty. Or considered empty because the opposite match field is a Number field.

 

5 hours ago, jon cohen said:

So to that end, did you do anything other than change the button to a script trigger?

I don't think so. I didn't even remove the Go to Field step (which I should have).

 

5 hours ago, jon cohen said:

Needless to say this is just a practice sandbox for the actual solution

Consider also other methods of selection, e.g. from a portal or a card window.

 

[jon cohen]

Thank you again for your very kind help.

I figured out the problem:

The calculation field was set to not calculate if the referenced fields were empty.  Obviously useless at the point where no selections have been made yet!

Once I changed that it worked fine!

Next task is to create a report listing every product totalling (and sorted by) how many times it's been chosen across all orders and highlighting those that never have...

I was going to create a TotalTimesChosen field in the Products table and have a script increment it each time it's added to a lineitem and reduce it each time it's deleted, but wondering if perhaps the same result could be more elegantly achieved by simply defining summary fields based on Count.

What do you think?

Edited September 29, 2023 by jon cohen

[comment]

43 minutes ago, jon cohen said:

create a report listing every product totalling (and sorted by) how many times it's been chosen across all orders and highlighting those that never have

If you want to include products that have no related line items, then your report must be produced from the Products table. A summary field will not be helpful here, but you can define a calculation field to count the related line items.

An important consideration here is how many products and how many line items do you expect to have. With large numbers, a solution that relies on unstored calculations can get slow.

Please start a new thread if you need more help with this.

 

 

[jon cohen]

Thanks (again!).

Will give this a go and start a new thread if further discussion needed.

🙏