you need to manually set a number since auto-enter will not work for you.
Unfortunately, filemaker 11 doesn't have ExecuteSQL(), which would make the calculation very easy.
For 11, you'll need to write a script that finds for the last set, gets the value, adds one to it, and then makes the new record.
Enter Find Mode
Set Field [ table::setNumber ; "*" ]
Sort Records [ table::setNumber ; Asc ]
Go To Record [ Last ]
Set Variable [ $set ; table::setNumber ]
Set Field [ table::setNumber ; $set + 1 ]
This would allow you to only increment the set number when you want via a script, and not as an auto-entry.
For kicks, here's how easy it is in FM14:
Set Field [ table::setNumber ; ExecuteSQL(SELECT MAX(setNumber) FROM table";"";"") + 1 ]
Life would be easier with a third table, "SetComponents".
The single Set has a PK, a name, a creation date, a purpose and that's about it. It's linked to n SetComponents, where a SetComponent has a PK, a FK_Set and a FK_Inventory. You alter a set's composition by adding SetComponents. A new Set will increment correctly his PK and will be ready to have Inventory items added to it, via a record in the SetComponents.
Good morning FM_DM,
I hope your day is going well. I lack Mike's experience, and I'm sure his method would work, but I was wondering if this might do what you need:
- Create a table with 3 fields (i.e. primary key, serialized number, and description)
- Use a Cartesian - cross join - to relate it to your existing table
- Create a value list based on the serialized number field
- Add a drop-down list to your existing table, which would reference the value list you just created, enabling to select the desired ID number.
I'm sure there are more elegant solutions you could implement to meet your needs, but we're all learning. Have a great day!
This worked great for what I need!. Its a much simpler solution than my own.
Good morning FM_DM,
Excellent! I'm glad you were able to get what you need. Have a great day!