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

[Macastronomer]

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.

 

[comment]

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.

[Macastronomer]

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...

[comment]

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.

 

[Macastronomer]

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.

[NLR]

 

 

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

 

[Macastronomer]

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).

Thanks

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.

EquipmentLog.fmp12.zip

[comment]

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.

 

[Macastronomer]

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.