script to tell me the maximum value in a field with a pop up text box

[thabart]

I am brand new to FMP, I have 11 days left on my trial before I have to make a case to my company to buy it. I am trying to replace an Access database because it randomly crashes. I need a little help.

Currently, my access database (a contract management database) has a form that has a button for adding a new record to a table. The table houses multiple types of contracts Non-Disclosure Agreements, Contracts, etc. My naming convention for those records are Non-Disclosure Agreements start with NDA, Contracts start with CON. The sequence is NDA-24-001, where the second set of characters is the year the agreement was fully executed, and the third is the next sequential number. For instance and the 4th NDA fully executed inn 2024 would be NDA-24-004.

 

When I click the button a dialog box pups up and asks What type of data is being entered (NDA, Contract, etc), a second dialog box asks what year the agreement was executed, based on those two answers the database then pops up a message that states the next number to be used. Such as "The last NDA entered was NDA-24-004"

 

I have been able to recreate the first steps in filemaker under a script entitled "Add Agreement" but cannot figure out the next step to find, and display, the last number of that type of agreement.

 

The code I have so far is:

____________________________________

Show Custom Dialog ["Agreement_Type"; "What type of agreement are you entering?"; $$Agreement_Type]

Set Variable [$$AGMTINIT;

Value: Case($$Agreement_Type = "NDA";"NDA-";$$Agreement_Type = "Contract";"CON-"]

Show Custom Dialog ["Income/Expenditure"; "Is this Income or Expenditure?";$$BUDVALUE]

Show Custom Dialog ["Year  initiated"; "What year was the " & $$Agreement_Type & " initiated?";$$Yearvalue

Set Variable [$$Yearvalue; value: Right ( $$Yearvalue; 2)]

Set Variable [$$COMPST; value: $$ABMTINIT & $$YEARVALUE]

Set Variable [$$rst1; value: Max(Header::Agreement Number)]

Go to layout ["frmHeader_Agreement" (Header; Animation:NONE]

Show Custom Dialog ["tester";"Tester"; $$RST1]

 

_______________________________________________________________

This is as far as I've gotten

The code in access to accomplish this would be something like

Set Variable [$$rst1; value: Max(Header::Agreement Number) where (Header::Agrement Number]) is like $$RST1

Any help to accomplish this would be truly appreciated.

 

Tim

 

 

[comment]

Filemaker has a simple and reliable mechanism to number all records in a table (regardless of type) sequentially:
https://help.claris.com/en/pro-help/content/automatic-data-entry.html

Your proposed numbering scheme, which would require maintaining a separate series for each type, is not simple to implement - esp. in a multi-user scenario where you run the danger of two users creating a new record at the same time and ending up getting the same serial number. This may not be a task you want to undertake if you are "brand new to FMP".

I would advise you to just number all your records sequentially. I doubt anyone will notice the difference.

Note also that you don't need a series of custom dialogs to get user input for a new record. They could just fill out the fields directly on a layout and commit the record when ready. Even with a custom dialog, the user can populate up to 3 fields/variables in a single step. And don't use global variables (prefixed with $$) where script variables (prefixed with $) will do.

 

Edited October 24, 2024 by comment

[comment]

Consider also what would happen if someone selects the wrong type when creating a new record and more records are created before the mistake is discovered.

 

[LaRetta]

10 hours ago, thabart said:

The sequence is NDA-24-001, where the second set of characters is the year the agreement was fully executed,

Hi Tim! 

Michael covered it well.  And this would concern me:  When you say 'fully executed' might the agreement start (record is created) on 12/31/2024 but wasn't completed until 1/2/2025?  There are other such scenarios as well.

After switching to filling the fields directly as Michael suggests, another idea is to create a display calc to generate the 'NDA-24-004' which is helpful to Users.  😀

And WELCOME, TIM!!  💥

Edited October 24, 2024 by LaRetta

[thabart]

I appreciate the responses. I am the only user for this database. Since each agreement has to have a unique agreement number, the best way to keep them separate is to allow that number to be the "key field" for that record. I am looking for a way to have filemaker parse by agreement type and then tell me the largest number for that set of data.

 

How would I create the display calc? That seems a step in the right direction.

 

Tim

[comment]

48 minutes ago, thabart said:

I appreciate the responses.

I am not sure you do, since you are so eager to disregard the warnings.

 

49 minutes ago, thabart said:

Since each agreement has to have a unique agreement number, the best way to keep them separate is to allow that number to be the "key field" for that record.

I strongly disagree. A primary key must satisfy two conditions: (a) it must be unique and (b) it must be immutable. In practical terms, this often translates to a meaningless value, such as a serial number or a UUID. A primary key that depends on another field does not satisfy these conditions (see 3NF). In your proposed scheme, the serial number depends on the type field = and as I already pointed out, if you modify the type you are very likely to get a duplicate, even if you are the only user.

 

1 hour ago, thabart said:

I am looking for a way to have filemaker parse by agreement type and then tell me the largest number for that set of data.

That part is actually easy. If you are scripting the process of creating a new record, you can find the previous records with the same type and get the value from the last one. Or use the ExecuteSQL() function to do the same thing. Or you could define a self-join relationship matching on type and get the last/max value from there.

 

1 hour ago, thabart said:

How would I create the display calc?

That's the easiest part, just do something like:

AgreementType & "-" & Right ( Year ( AgreementDate ) ; 2 ) & SerialIncrement ( "-000" ; SerialNumber )

but that's something you do at the very end, so it's hardly "a step in the right direction".

 

And we haven't even mentioned the need to reset the serial numbers at the beginning of each year.

 

 

[thabart]

Wow, that was quite the response, let me make sure I understand your intent.

 

Since I didn't immediately agree with your statement, you state I was eager to disregard warnings and question my sincerity in being appreciative.

The Primary key does not depend on another field, it is assigned when I create the record and is hard coded.  For instance, if the agreement type in a contract the first three letters of the primary key is CON. It is not dependent on the type field as it is not a concatenation of any other fields. Since they are assigned based on signed Agreements, they are not changed.

 

Putting that aside, your comment of

 

"That part is actually easy. If you are scripting the process of creating a new record, you can find the previous records with the same type and get the value from the last one. Or use the ExecuteSQL() function to do the same thing. Or you could define a self-join relationship matching on type and get the last/max value from there." is exactly what I was asking for help with.

 

I am very familiar with SQL but am looking for help writing that specific SQL statement.

 

Tim

 

 

Edited October 24, 2024 by thabart

[comment]

I do not question your sincerity. I was merely observing the fact that you do not appreciate the dangers I have pointed out.

 

13 minutes ago, thabart said:

The Primary key does not depend on another field

That is simply not true. If you have 3 records of type NDA and 5 records of type CON, then the next serial number will be derived as either 4 or 6, depending on the value selected for the type field. You say it won't be changed, but I don't see how you can rule out a simple human error.

 

19 minutes ago, thabart said:

looking for help writing that specific SQL statement.

See the attached demo. Note that you can use a variable instead of the global field as the argument.

 

SQL_MaxByType.fmp12

[thabart]

Thank you.

 

Tim

[Ocean West]

This might be helpful in generating sequences numbers if you script it and use UUID for relatiionships. https://scalefm.com/sequence-generator/