2 Replies Latest reply on Jun 8, 2011 7:44 PM by symbister

    Conditional Value Lists....(again)

    symbister

      Title

      Conditional Value Lists....(again)

      Post

      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....

      Picture_2.png

        • 1. Re: Conditional Value Lists....(again)
          philmodjunk

          If a tag in artworks is setting the value of a field to "sold", then the first option should work for you:

          If (Artworks::SalesStatus = "Sold" ; Artworks::ArtworksID )

          The trick to the second option is to add a field in lineitems that uses a calculation to produce a constant value that matches only to your "sold" artworks.

          Define a calculation field constSold as a calculation field. Enter "Sold" as its calculation.

          Define a relationship:

          LineItems::constSold = SoldArtworks::SalesStatus

          Then your conditional value list can draw values from SoldArtworks, with LineItems as the "starting from" table occurrence.

          (SoldArtworks is a new table occurrence of artworks you create by selecting it, then clicking the button with two green plus signs.)

          • 2. Re: Conditional Value Lists....(again)
            symbister

            Hi Phil - thanks again