Jump to content

Need to reset and increment a serialized field that contains a string rather than a number

Recommended Posts

Thank you in advance...

We have a serialized field that has a value of FL2021-0001 and increments by +1. 

I created a script that makes a duplicate record for reasons that don't matter here... but at this point I need to reset the serialized field to the next correct number. I do this with the Set Next Value function as such: 

Set Next Serial Value [TableFL::SerializedField ; Max ( TableFL::SerializedField ) + 1 ]

The problem is it sets the next value to 20210002 instead of FL2021-0002.

Can I read this as a string and increment it or is the return always going to be a strict numeric value?

Thanks again.


Link to post
Share on other sites

The context here is not quite clear. Assuming that you have a relationship to TableFL that matches all records in this table, and that SerializedField is a Text field, you should be getting a result in the form of  "FL2021-0001" in response to:

Max ( TableFL::SerializedField ) 

If so, then the problem you report is caused by your forcing a numerical operation on a text value. Instead of adding 1, use the SerialIncrement() function to increment the value by 1:

SerialIncrement ( Max ( TableFL::SerializedField ) ; 1 )


A warning is due here: resetting the serial number should not be a routine operation. It is quite easy to get a duplicate value when relying on the maximum in previously committed records. You did not tell us what is the purpose of this exercise but there is very likely a better way to accomplish it.

Link to post
Share on other sites

That's what I thought was happening but I didn't know how to address it. Thank you for your reply it worked perfection... and then uncovered another flaw in my design.

I need the number to increment the highest number in the list. Instead it increments from the last number currently listed. I don't necessarily want to sort (which is one way to solve it), but I want it to look at that field increment 1 past the greatest value (which is what I thought MAX did, but it doesn't).

I really need to learn more functions...

Link to post
Share on other sites
2 minutes ago, Macastronomer said:

I need the number to increment the highest number in the list. Instead it increments from the last number currently listed.

I am afraid I cannot help you with that without understanding the context. The Max() function works on related records - and the relationship does not have to be sorted. If you are getting a different result, then your starting point is not what I assumed above.


Link to post
Share on other sites

After I anonymize some info and strip the DB down to what I need to show, I'll send the file so you can see what it is I am up to. Hopefully I can get that done tomorrow. What I'm trying to do is too complicated to explain without an example.

I don't doubt that there is a better way to do it. I'm open to any and all suggestions.

Thanks again.

Link to post
Share on other sites



I've made a script that does the job.  (Done in the old FM-11 script format but will still work with current Filemaker)



Link to post
Share on other sites

NLR, that worked. Thank you.

I wrote previously that I would explain the point of what I'm doing in case there is a better way to accomplish it. Here's the file, which I made up quickly—I hope it works as I've intended.

Open as guest.

New log entries are made under the Flow_log layout. Start and Stop times are input used to produced information for billing.

The FL # is not the key field for this table, it's just a way to create a log id for the task being done. There can be duplicate FL #s when the records pertain to the same samples.

This request addresses an issue when a tech partially runs a sample (which has a start and stop time) and then later completes it (with another start and stop time). I created a button that runs (Break/ContRecord) which duplicates the record, inputs the same FL#, and deletes data in some fields. Thanks to the solution, the iteration of the FL # now continues as it should.

If there is a better way to add a second record with the same FL# that doesn't affect the serial increment of the FL# field; or a better way to accomplish continuing a log record, I'm open to suggestions (and learning).


PS. Names have been changed to protect the guilty, any similarity to real life is coincidental, and no animals were injured in the making of this file.


Link to post
Share on other sites

I am not sure I follow your explanation. If  - as it seems - you want to record multiple events pertaining to the same sample, then you are describing a one-to-many relationship - and this of course should be handled by having a parent table and a child table - not by creating sibling parents with the same serial number.


Link to post
Share on other sites

I see what you are saying, but these records are only used to format the data and export to a spreadsheet that will be uploaded to a completely different system. At that time the records are combined for each account into a single, monthly charge (per billing item). Granted, these will be saved for a while in case a client calls back after billing and says, "why was I billed $X?" and we can search their account and say, "these were the hours, etc...." After that it's all done. The info is primarily held in a different system.

I might still consider your recommendation before this is implemented—if it is implemented. That group is attached to their pencils and clipboards.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By milky
      Hi All,
      Just had a look at the new Add-ons in Fm19.
      Is there a way to make the timer work in reverse.
      i.e. Start from a field (Timer::TimeLength) that a user can input, so that the specified time of say 30:00 will run down to 0:00
      Any help would be appreciated.
    • By BostonFM
      I am trying to script an import of three worksheets in the same Excel file. The 3 geographical regions worksheets are not structured the same, i.e. field name rows are not in the same place and are named differently.
      The script runs but stops to ask which worksheet to process.
      How do I create a variable/parameter in the script for it to proceed sequentially from worksheet 1-3 without stopping?
    • By SupportGuys89
      Hello Fellow FM'ers!
      We have been testing Linux and everything has been great, but we are having issues with the authentication via web direct using Windows Federated Services. We followed the steps in the available documentation "Addendum4_ADFS.pdf" and also used "ExtendOAuth_v1.pdf" to get the AD-FS rules configured as well as the necessary admin console requirements (Client Key, Shared Key etc...) We do see the following on attempting to login to our solution:

      And when we click on the AD FS Option we do receive a login prompt from our Federated Services:

      However, even with the correct credentials we still see the following error message:

      We have reviewed the logs within AD-FS and no errors are reported and the credentials are passed as expected. Within our solution and Active Directory, we also ensured the user is in the correct group to access our solution via webdirect. I will gladly provide more details into the configuration of AD-FS or FM Admin Console, but these all match the documentation provided by Claris. 
      We went a bit further and got an inhouse developer to take a look and test the flow and he was able to confirm the following:
      Our Endpoints are setup correctly and can be viewed internally @"https://INTERNALADFS/adfs/.well-known/openid-configuration" Initial request  was tested with "https://oidcdebugger.com/"  Postman was then used to test once we received a response. Decoding the jwt received with "https://jwt.ms/"
        The JWT had a unique_name, an email, and a groups array with one of the values in that group array being the group that is set up in the Filemaker solution.   With that said, we are not sure what the next step would be to test this as we appear to be seeing all the correct information in our testing yet we still are not being "authenticated" in our solution. I have also opened a ticket with FIleMaker and no luck as of yet. Any insight would be greatly appreciated!  
    • By Deepak Kumar
      Hello Everyone,
      I would like to check what are issues when FileMaker Pro and Server 19 are installed in Windows Server 2012 R2?
    • By naio
      The content of myField is:
      text before quotes "quoted text" and I want to format a JSON object:
      JSONFormatElements ( "{\"object\":\"" & myTable::myField & "\"}") the quotes within my data make invalid JSON, so I escape quotes:
      JSONFormatElements ( "{\"object\":\"" & Substitute ( myTable::myField; "\""; "\\\"")  & "\"}")
      Then I get valid JSON like this:
      {"object" : "text before quotes \"\u0016quoted text\u0016\""} I wonder where these \u0016 characters added around the quoted text come from, I don't want them there
      How should I escape quotes without this problem?
  • Who Viewed the Topic

    10 members have viewed this topic:
    ArnaldoRoberto  ratherbsailing  PowerSystem  jayivan  Steve Martino  comment  Hee  dbservices  bcooney  normanicus 
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.