1 Reply Latest reply on Oct 14, 2013 10:09 AM by philmodjunk

    Calculation serial number entry



      Calculation serial number entry


           I'm creating a recipe database. I want to classify each entry by Menu Class (number) and serial number (auto-entry).


           I have a value list with "Meat, Poultry, Seafood, etc" My thought was to create a calculation field to turn the prefix into a number (ie Meat = 01-, Poultry =02- and combine this with a serial number value. This seems pretty easy, I would like to have a Meat ID of 01-0001 and a Poultry ID of 02-0001. But I don't see that working. The next record, if Poultry would be 02-0002.


           What I am having trouble with is with the calculation field which is IF the Category = "Meat" than "01-". It looks as if I can't change this number once entered. If I enter Category "Meat' and get a prefix of 01-, but realize this is actually a Poultry item. When I change from Meat to Poultry I want the prefix to change to 02-, but it remains the original entry of 01-

        • 1. Re: Calculation serial number entry

               It's not a good one to use as your Primary key for use in relationship links to other tables of data. It may however, need to be made to support special labeling needs--possibly in support of existing procedures already practiced by your end users. In such cases, you can set up this field as a data field so that it can be used in searches, sorts, reports, etc.--just not as a match field in relationships.

               You can Divide this serial number into two fields, one with the category value and one with the sequence number. You can then set up a self join relationship that matches by category field so that the following calculation can be used to assign the next sequence number value:

               Max ( SelfJoinTableOccurrence::SequenceNumber ) + 1