you can create or in the same field, make a calculation type field and do a if formula and else formula for that if you send me some example i will be glad to halp you making the formula
Thanks Erik. I'm not sure what example I could send to you? I've taken a screenshot of the data entry form. At the bottom blue section, you can see that the Donation Category is filled in (using a drop-down) and the field next to it, Item Number is empty. I'd like that field to fill in based on the first 3 or 4 digits from Donation Category. Should I set the Item Number field as a number, then go to Options? Under the Auto-Enter I can see that there is a Calculated Value, but I'm not certain how to write the Specify Calculation. Or is there another way to do this?
With a self join relationship, you can match records by category and then Max ( RelatedTableOccurrence::serialnumber ) + 1 will provide the next number in the series.
But if you have multiple people adding/editing records at the same time, there's a chance that two records could get the same number. If you have only oe person doing this, it's not an issue.
I'd use a script to assign this value and it can include code to set the initial value when there are no previous records of the same category.
Soooo...from what I am reading and based upon my capabilities, a script sounds like the best option. Once all of the items are entered, I can have a script sort based on category, then assign numbers from there. We will have multiple people doing data entry, so I can't be assured of redundancy.
I'm thinking a script such as the following? I would repeat this for each donation item category, of which I have 14. Does this make sense?
Please note that I have no formal training in Filemaker and I have figured this out on my own without any computer background, so much of what you say I have to look up and figure out. Simpler is better for me as well as being explicit. Thanks so much!
You should only need one script, not one for each category.
Replace Field Contents modifies the field for all records in your found set so this seems likely to change records to values in ways that won't work for you and don't you have all these items from all categories in a common table? that would make the combination of Show All Records and Replace Field Contents especially catastrophic--especially if two or more people do this at the same time as it would result in frequent errors due to edit locks on individual records.
What I suggested would use a self join relationship such as:
TBSAuction::Category = TBSAuction|SameCat::Category
where TBSAuction and TBSAuction|SameCat are two Tutorial: What are Table Occurrences? with the same data source table.
This sets up a script for assigning an auction number like this:
Set Variable [$Maxvalue ; Max ( TBSAuction|SameCat::AuctionNumber ) ]
IF [ $MaxValue = 0 // first item in this category ]
Set FIeld [ TBSAuction::AuctionNumber ; Case ( // put calculation here that uses category to determine the initial value for that category) ]
Set Field [ TBSAuction::AuctionNumber ; $MaxValue + 1 ]
This auctionNumber should not be used as a primary key to link records in TBSAuction to other tables. Use an auto-entered serial number field for that purpose and an unique values validation option should be specified for AuctionNumber to ensure that the values given to it are unique.