Conditional Value Lists....(again)
Hi, using a mix of FMP 8.5,9 and 11 on various macs, served from FM Server.
I've done a fair amount of searching for this - and yes Phil I've trolled through most of your excellent help posts, but still can't figure it...
Setting up a Sales process for individual artworks, with Invoices-----<Line_Items>------Artworks - OK.
Once we've sold an item, how do I constrain that item being selectable in future? I've got Value Lists set for Artwork_ID and Artwork_Title, to be selected on the Invoice in a portal to Line-Items.
Once an item has an 'Amount_paid' against it - it's sold and should no longer be available in the drop-down on the portal...I've played around with a couple of previous suggestions:
Define a calculation field that only returns a value if other conditions are met and base your value list on this field instead of the original data field.
Example: If ( include = "yes"; valuefield ; "" )
This probably won't work for you as this field must be a stored indexed field and your "today's date" based requirement can't easily be included in a stored calculation.
Option 2: More work to setup but much more flexible
Define a relationship that correctly limits the number of records in your value list table to just those records that contain the values you want. Basically, if you can place a portal on your layout that lists the records you want to see in the value list, then you can use this relationship in your value list definition.
When setting this up, specify the values as coming from the Table Occurrence you used in the above relationship and which points to the table containing your values. Select the "include only related values starting from" option and select the other Table Occurrence (the one that the first table occurrence connects to in your relationships graph) as your "starting from" choice. Usually, this second table occurrence is the same as the Table Occurrence your layout refers to.
but having difficulty identifying how I can set up such a relationship where if a tag in the Artworks table is set to Sold, don't include it in the Value List, and this tag is only set to Sold if Amount_paid is not zero. Am I making sense?
Screenshot is of the portal on the Invoices layout, single row.
any help appreciated....