The way you have set the Max funstion is finding the maximum value of that field, in that record. I assume that is ot what you want. If you want to find the maximum value of that field across all the records in that table, then you need to create a self-join relationship for that table, by a Cartesian (X) join.
Then set the calculation to be Max (SelfJoinRelationship::Field) + 1
Thanks for the reply. I do not believe I need a selfjoin replationship because I am just looking for the max value of one field. That field though is being populated from many other tables, not sure if that matters.
Essentially I have an ID table and the ID field in that table is the primary key that is linked to all my other tables. The other tables each have a field called "catalog number". When these fields are are populated, through a calculation and the primary key this then populates the ID table (in the catalog number field.) I am trying to make it so when a user clicks "next ID number" on any of the layouts, then the highest catalog number is pulled from the ID table's field with "+1" added to it. Does this make sense?
Thanks for the help!
I don't understand whether your list of IDs is tracked in the ID table, or not. If it is tracked in the ID table, and each other table can create a new ID to be listed and controlled by that table, then you need a relationship, one way or the other, that sees all of the IDs in the ID Table. I would suggest you still need a self-join from the ID table to itself, so that it can know what the max value is.
"If you want to find the maximum value of that field across all the records in that table, then you need to create a self-join relationship for that table, by a Cartesian (X) join"
I see now! I still don't understand why you would want the max of one field in one record because in my case that is only one value, but I do now understand that a self-join relationship is necessary and I was able to create one with the ID table. My question now is about using the calculation. Do I put this in the catalog number field's calculation? I have not used a self join before, and I am confused because there is still only one table, so I can't set a variable from one table occurence to the next (nor would it be nessecary). Perhaps you could elaberate a bit, or if you know of an artical in the knowledge base I can refer to? I found very little on this subject when I searched. I would like to see an example of self-join relationship being used in a script.
A bit more complete description of your tables and relationships would be helpful. A screen shot of Manage | Database | Relationships is one way to do that and can be uploaded with the controls found below Post A Answer.
An auto-entered serial number might be a much less problematic method for generating your series of ID numbers. It's designed for that purpose after all and won't need any scripting to pull off, nor will multiple users creating records at the same time produce duplicate ID's--something that becomes possible when you use other methods.
I'm sorry, I was trying to just ask a simple question. I attached a screenshot of part of the ERD (Phil you will see I've re-done the relationships completely). And I have a script that says:
If [ID Table::Next ID # ="Next ID Number"]
Go to layout [ID Table (ID Table)]
Set Variable [$Next ID Number; Value:Max(ID Table::Catalog #)+1]
Go to layout [Rocks (Rock Table)]
Set Field [Rock Table::Catalog #;$Next Id Number]
I also created a self-joining relationship between the ID table and "ID Table 2" and tried to put in the calculation field of ID Table:: Catalog # "Max(ID Table 2::Catalog #)+1" But it seems no matter what I do the Catalog Number fields keep returning "1".
Oh and the ID number is auto-entered as serial and works fine, the ID number is the PK for all the tables with a relationship to ID Table. What I am trying to do is make it so the user can click "Next ID Number" and the next catalog number will populate in the table associated with whatever layout they are on. To do this I think I need to find the maximum value for catalog number in the ID table and then set that value to "+1".
But why do they need this? How is this "next ID number" used? What does it identify?
Well this database is for collections of objects, and each object could have a catalog number but its not gauranteed (so catalog number can't be the PK). The "Next ID Number" function is so that a user can find out what the next catalog number is. They could keep track of this on their own but then there is a chance they will lose it, and when many people are working on the same database this would mean they would have to find out who knew the last number they used to catalog with before they could enter in data for that object. I could auto-serial the catalog number but as you have pointed out before that doesnt work when importing numbers because say the last catalog number imported was "100" then if it was set to auto-serial number the next number might be "1" not "101". I thought using the "Max" feature would allow me to get the next catalog number easily but it is proving to be much more difficult than I anticipated.
Another thought I had was to do a "sort" and then "go to record last" and "set variable" from there, I havn't tried this yet.
I didn't intend to say that it wouldn't work for importing records, but rather that you have to do some additional work to manage the imported data to update serial number settings after importing the data.
I'd still use the auto-entered serial number. After importing records, you can use a script to determine the maximum value in your table and then use the set next serial value to update the serial number setting on your field.
The methods you've been discussing in this thread risk generating duplicate values if two or more users try to get the "next ID number" at the same time.
I can try that. When you say "you can use a script to determine the maximum value in your table". Can you give me an example of this script? I'm still not understanding the maximum function in scripts very well.
Define a field of type Summary, not a calculation field with Max(). Set it up to compute the "Maximum" of your serial number field.
After an import or any time that you want to update the next serial value setting:
Go to Layout [layout based on table where ID field is defined]
Show All Records
Set next Serial Value [YourTable::YourSerialNumber field ; YourTable::SummaryField + 1 ]
I'm still not understanding the maximum function in scripts very well
The following is true for all the aggregate functions such as max, Average, Count, Sum, list, etc...
An aggregate function works on a list of values. The syntax and the field storage options will determine the results.
The simplest syntax is:
Sum ( Field1 ; field2 ; field3 )
Which works on the specifically listed fields all from the same record.
If you were to use:
Max ( Field1 ) or Max ( table::Field1 ) -- with both field 1 and the calculation defined in the same table
You are only sending a single value to the function. This is like asking for the maximum of "2"--which will return the value 2.
It's also possible to use
Count ( Field1 )
where Field1 is a repeating field. Then the aggregate function works on all the repetitions of the repeating field. In this case, count will count the number of repetitions that are not empty.
The most common syntax is:
Max ( RelatedTable::Field )
In this case, the list of values returned to the aggregate function are from the set of related records that are related to the current record of the table where you defined the Max function. It's helpful to picture an unfiltered portal based on RelatedTable. An aggregate function would then use all the values you'd see in such a portal to compute a value.
Brilliant Phil! That worked perfectly. The only flaw yet is one of my own. I shall mention it here in case you have thoughts but it is much less important that everything so far.
The last (I hope) issue is that because my layouts are set to run a custom dialog that says "Do you want to save these changes?" on record commit, when the user clicks "next ID number" they must first select "Yes I want to save these changes" before they can see that the number indeed populated the field. Since I have to leave that layout to set the Catalog Number to +1 in the ID Table it says "Do you want to save these changes" before the number is even available to see (so I can't just delay by going to the next field). I need my Change Scripts as I call them, to run because that is creating a history of important changes (such as a new catalog number).
Why are you setting the catalog number to + 1 in a script? If two users do this at the same time, they will each get the same catalog number.
What you should do is have your script change layouts to the table with the serial number, and create a new record in that table--generating the next serial number in the series. This can then be copied back to the layout and record from which you started if that is needed.
You may actually be doing that, but it's important to be sure as othewise you are setting your system up for trouble once it is hosted over a network.
Do you really need to have 'auto save' disabled for your layouts?
You could use a commit records step with the no dialog option to suppress the "do you want to save?" message, but you are then committing the record and this may have other undesirable consequences.