This requires a special conditional value list--called a diminishing value list.
The trick is to use a relationship to your products table that only matches to products records that don't have a record in your line items portal and then use this relationship in the conditional value list to limit what items are listed.
Do you know how to set up the typical conditional value list?
Have you every used a return separated field of values as a match field in a relationship?
And this also can be done with ExecuteSQL in FileMaker 12 to simplify your relationship graph a bit.
Here's the "exclusion list" calculation field I use with the ≠ operator (instead of = ) in a relationship to make this work:
List ( -1 ; List ( LineItems::_fkProductID ) ) // clear the do not evaluate if all referenced fields are empty check box.
The -1 is a value that doesn't match to any product ID so I am sure to always have at least one value in this match field.
That then is used in a relationship like this:
Invoices::cExclusionList ≠ Products|notChosen::__PkProductID
and my value list lists product IDs from Products|notChosen, include only related values starting from Invoices.
And if memory serves, there may need to be a refresh window step performed by script trigger after selecting a product before the value list updates...
For the ExecuteSQL version, see: Diminishing Value Lists and ExecuteSQL
Thanks Phil. I like both options. I tried the exclusion list, but I'm keep getting 'index missing' when I got to my data entry screen.
Sounds like the exclusion list is on the wrong side of your relationship. It's an unstored and thus unindexed calculation field. It has to be defined in the table on the "one" side of the relationship (Invoices) not the "many" side (Products).
Just to make sure I'm doing this right. This is all being done on the Ipad layout 'Invoice Details'.
I created a field in the 'Invoices' table (I'm using the starter solution 'Invoices') called 'cExclusionList' as a calculation with the definition set to return a calculation result as 'Text'.
The calculation is 'List ( -1 ; List ( Invoice Data::Item). The do not evaluate if all referenced fields are empty check box.
I duplicated my products table, named it 'Products|NotChosen'. I linked the relationship as "Invoices::cExclusionList ≠ Products|Not Chosen::Product Id".
I created a new value list, and the value is 'Field:Products|Not Chosen::Item' because I want to show the item names on my form. I changed the properties on the layout to show values from the new value list. I added a 'perform script step' to trigger 'OnObjectExit' to perform a refresh window script and it still allows me to select the item name more than once.
The do not evaluate if all referenced fields are empty check box.
???? Make sure that this check box is cleared.
I duplicated my products table, named it 'Products|NotChosen'.
I hope that you actually did NOT duplicate the table, but that you duplicated the Tutorial: What are Table Occurrences? for Products.
We have some confusion over "item" and "Product ID". Your value list should specify Product ID as field 1 and Item as Field 2. Your exclusion list should then also refer to product ID and not Item. Note that the relationship matches to Product ID with the not equals operator, not item.
'List ( -1 ; List ( Invoice Data::Product ID)