1 Reply Latest reply on Nov 25, 2011 8:52 AM by philmodjunk

    A non-changing list of works. Need help...



      A non-changing list of works. Need help...



      Since it's my first post here, greetings to all FM developers.

      Now, here's what I'm willing to achieve, completely without a result.

      I'm trying to set up an Offer Creation Layout. First things on it are Client's ID, and a ObjectID. Easy stuff.

      Then, there should be a constant list of all aviable works that I can do on an object. It really has something about 70 positions.

      The point is to show all positions of the list on a layout and than select some that will be done ( yes/no field )

      About the constants:

      Each position should have a name ( and an ID? ) that is the same for all Offers I create.

      Each position has a price. Each position must have 4 additional fields for part quota ( used from my magazine, new from my magazine, used from clients magazine, new from clients magazine ).

      I want also the list of works to be extendable - so I can add a new position to it.


      I thought of a table in which I would store all works as records and show it as a portal on the Offer Layout.

      But then, how to maintain possibility of changing the part quota or the 'yes/no' work field for different offers without changing the previous ones?

      Probably another table with Offer ID and Work ID. But then I won't have a possibility to display all Works on the Offer layout?


      I also thought of a stand-alone table storing works as records ( and each record would have OfferID), and than copying them all into a table related to the Offer, with a script that would fill the OfferID in the copied records.

      But then, after filling 10 Offers, the table would have 700 records...


      The fact is, I just can't think of a right relationship ( or right relationships ) and a table set...

      I hope I described my problem well enough...


      I would be grateful for any help,




        • 1. Re: A non-changing list of works. Need help...

          Setting up the correct tables and relationships need to be the first step, then you can figure out from there what options to use for designing the user interface to enter, edit and view the data stored in those tables.

          To help you do that, we need to know more about what you are trying to do.

          I sounds like you are selling something (advertising?)

          Most sales systems are built around the following tables and relationships:


          The Products/Services is the table you use to keep track of the items and/or services you offer to your customers. Invoices represents a single sales transaction. And LineItems is used to list the individual goods and services purchased/rented on a given invoice.

          It sounds like your "unchanging list" would be a list of records from the Products/Services table. The specific services selected for your customer (the yes/no and position fields) would be stored in lineItems. This is the table and relationship structure you'll find in the invoices starter solution that comes with FileMaker and also in this much simpler demo file created by Comment:  http://fmforums.com/forum/showpost.php?post/309136/

          While this is usually implemented with a portal to lineitems and a drop down list of products/services, it is also possible, with scripting, to set up a list of items from the Products/Services table in a portal that you can click to select them for a given invoice. The script then creates a record in LineItems when you click a row in the portal.

          Thus, while "700 records" is actually a very small number of records (not unheard of to have over a million records in a lineitems table), the lineitems table would only list the actual goods or services selected for that invoice rather than a copy of every item offerred.