3 Replies Latest reply on Jul 23, 2014 8:44 AM by philmodjunk

    IDs and consistancy between versions



      IDs and consistancy between versions


           I'm working up a solution that uses several pop-up fields. It is certain that the products populating those fields, along with their position in a list will change over time. So for example:

           1. In 2014 there might be products A to M but by 2015 products B, F, J-K had been dropped and N-Q added. or

           2. In 2015 products G+H were still present with the same name, but have a different composition.

           What methods might be used to:

           1. Update a product list but permit calculations with obsolete products for older record or at least keep the values generated from those calculations.

           2. Ensure calculations occur based on the corrrect product (ie. a calc from 2014 should be with the 2014 product, not the updated 2015 one).

        • 1. Re: IDs and consistancy between versions

               1) And what tables, fields, etc do you have in your database? The question appears to indicate that you have more than one table or the "calculations" with obsolete products will disappear if you delete the products records for those obsolete products. So there are two ways to keep that data: Put the data in a related table so that the record with that data is not deleted when the product record is deleted. or Don't ever delete the product record. Set a value in a field to mark it as "obsolete" so that it no longer appears in your reports, value lists, etc, but the data is still there for use in these cases.

               2) Use auto-entered serial numbers or Get ( UUID ) to uniquely identify each product and to use in relationships to link those products to other tables. Never, EVER change the value of these unique identifiers once they are assigned to a given newly created product record. If you need to change some other identifier, do so, but not as the field used in relationships.

          • 2. Re: IDs and consistancy between versions

                 Yes, it would be better to keep the products permanently, hide them as they become obsolete but permit old records to recall them to produce a relevant calculation. Is it feasible that I make a conditional value list determined by 'obsolete/not' so that the product pop-up list will only show current products; but will calcs work for products that can't be recalled to the list?

            • 3. Re: IDs and consistancy between versions

                   It's quite feasible. See Option 1 in the first link of the list in this post for a simple method that does not even require a relationship to set up.

                   Here are some links on conditional value lists. (Sorry for the large mass of data all at once here, but it's a useful reference list you can explore as needed.)

                   There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                   The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

                   Forum Tutorial: Custom Value List?

                   Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                   Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                   Hierarchical Conditional Value lists: Conditional Value List Question

                   Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                   Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.