serial numbers and UI updates

I have been looking at the various posts for this topic and have gotten to this point. Thank you to all.

The serial numbers include a small amount of text at the beginning for identification. We need to parse out the number, add 1 and rebuild the correct serial number to insert.

Below is what we have tried, but the problem is that the Left and Right steps (3rd & 4th lines below) are not getting the field values.

They are instead, splitting the field name, ie. "OPTION" (left) and "ons_id" (right)

The desired result should be: "optn00" (left) and "002378" (right) for "optn00002378"

Go to Field [OPTIONS::kp_options_id]

Set Variable [$primarykey; Value:Get ( ActiveFieldName ) & "::" & Get ( ActiveFieldTableName )

Set Variable [$primarykeyLeft; Value:GetAsText ( Left ( $primarykey ; 6 ) )]

Set Variable [$primarykeyRight; Value:GetAsText ( Right ( $primarykey ; 6 ) )]

Set Variable [$NEXTprimarykeyRight; Value:$primarykeyRight + 1

Set Variable [$NEXTprimarykey; Value:$primarykeyLeft & $NEXTprimarykeyRight

Set Next Serial Value [OPTIONS::kp_options_id;$NEXTprimarykey]

The following didn't work either (data viewer showed ?)

GetAsText ( Left ( Evaluate ( $primarykey ) ; 6 ) )

Any help is greatly appreciated,

idee

This is a lesson I learned a long time ago - don't use text in serial numbers, especially if they are supposed to be numbers as FM will drop leading zeros when the number part is extracted. These days all my serial numbers are strictly numbers and start from 1.

However..

Set Variable [$variable; Value:Substitute ( SERIALNUMBER; GetAsNumber ( SERIALNUMBER ) ; "x" )]

Set Variable [$variable; Value:Substitute ( $variable ; "x" ; GetAsNumber ( SERIALNUMBER ) + 1 )]

Just substitute $variable and SERIALNUMBER to suit your application.

Edited July 31, 200916 yr by Guest

Thanks, this should help. I may go in and change out the serial numbers to eliminate the alphas. That logic came from a training class. It made sense back then ...

The bigger question:

Is there a way to call the SERIALNUMBER field using a variable within a Get function? That is what I am hoping to discover.

Could you expand on what are you actually trying to do? I don't really understand what you mean by:

We need to parse out the number, add 1 and rebuild the correct serial number to insert.

In general, if you have a serial ID like "OPTION999" you can easily add 1 to it by =

SerialIncrement ( SerialID ; 1 )

This will return "OPTION1000" without any parsing, etc.

---

BTW,

Go to Field [OPTIONS::kp_options_id]

Set Variable [$primarykey; Value:Get ( ActiveFieldName ) & "::" & Get ( ActiveFieldTableName )

seems like a rather roundabout way for:

Set Variable [$primarykey; Value:OPTIONS::kp_options_id ]

Perhaps your entire script could be reduced to:

Show All Records

Go to Record [Last]

Set Next Serial Value [ OPTIONS::kp_options_id ; SerialIncrement ( OPTIONS::kp_options_id ; 1 ) ]

Edited July 31, 200916 yr by Guest

comment, thanks. that looks great. I will try it. It does simplify things.

I am still hanging on the hope that I can use a variable to identify the field in the calc. In what you placed here, can I use a variable to call the (in this case "OPTIONS::kp_options_id"? I have multiple tables that will need to be set to in the script. I was trying to automate it if possible.

I am not sure you can fully automate this (you haven't answered my question about what "this" is).

Variable or not, you must point to a field from which to get the initial value. Presumably (if your TO's and ID fields are named consistently) this reference could be calculated from the current context.

However, when you use Set Next Serial Value [] you must reference the target field explicitly - so there's no point in trying to make the first part dynamic.

As to your question, sorry. This is a script to import data into an updated UI. There is an active dB file, and also the development version on my desktop. I have 3 scripts build to update the database.

The first runs from the development file and "saves as" a copy then removes all data. This is similar to the clone, but I am having trouble with clone right now. (see http://fmforums.com/forum/showtopic.php?tid/210246/post/336606/hl/global/fromsearch/1/ If I clone the file, I get this problem. If I save a copy and clean it manually, everything is fine. )

The second is run from the active file, and just creates an export of each table to an excel spread sheet in a file on my desktop. I have chosen excel because it allow an import to match fields, whereas tab and cvs do not.

The third is run from the new copy and imports the data files. As a part of that process it needs to check the serial number record and update that number as well. This part of the script is what I was asking about. Instead of having to change multiple lines for each of multiple tables, I was hoping to create a variable to only input the specific field once.

But looking at what you presented:

Show All Records

Go to Record [Last]

Set Next Serial Value [ OPTIONS::kp_options_id ; SerialIncrement ( OPTIONS::kp_options_id ; 1 ) ]

That should require only two inputs. Right? If so that would be the best solution, and simplest. What I was breaking down to multiple lines you did in three.

thanks

earlier in this post it was mentioned that this only worked with pure numeric serails, not with alpha/num mixed. Is that correct? Will yours work with a mix? That is why I was breaking it apart and rebuilding it.

Edited August 3, 200916 yr by Guest
added question

I am sorry. I think the answer was in the early post but I was too dense to see it. IdealData said to beware of alpha mixed serial numbers, and I took that to mean that they wouldn't work unless they were purely numeric. That was wrong, correct? These commands will make the change even with the alphas? Then Comment gave a similar help. Sorry, but the trees in my mind were too thick. I was stuck on the variable question. I am giving up on that.

So will Set Next Serial Value update the alpha/number serial number?

I am back to the desktop and will give them a go. Thanks for your help.

will Set Next Serial Value update the alpha/number serial number?

Yes, provided you use the SerialIncrement() function to increase the last serial.

worked great. thanks

amazing what you can see when a few of the trees are taken down.