Convert data from one form to another

[Hoib]

I'm on FM Pro Adv v11.

I have Table1. It has a field called "_id" which already contains variable length text data: example - 1234.

I'd like to convert this data to text in the form: HID00001234. That's fixed 11 positions.

I've tried Replace Field contents using a Calc value stipulating:

Table1_id = Table1:_id + "HID00000000"

thinking that it would "add" whatever is in _id to the string "HID00000000".

But I realized I cannot "add" what isn't a number. And I don't want to concatenate.

Is this a script function? Or would there be something built in to FM to accomplish this?

[Ocean West]

you want the Number existing in the field to be padded out 11 characters always starting with HID########.

Backup first - but this should do the trick -

"HID" & Right ( "00000000" & Filter ( table1::_id ; "0123456789" ) ; 8 )

we prefex the output with "HID" using the right function we take 8 right characters of the concatenated value what ever numbers are filtered

[comment]

I have Table1. It has a field called "_id" which already contains variable length text data: example - 1234.

I'd like to convert this data to text in the form: HID00001234. That's fixed 11 positions.

I've tried Replace Field contents using a Calc value stipulating:

Table1_id = Table1:_id + "HID00000000"

Try =

SerialIncrement ( "HID00000000" ; Table1:_id )

Edited October 28, 2012 by comment

[Ocean West]

:worship: elegant!

[comment]

:idunno: Built-in...

[Hoib]

Boy! Fabulous! Just amazingly fabulous! A built in FM goodie. You know I actually looked at it but not long enough.

Just a small typo in the Function but that was easy to find and fix.

You folks always seem to come up with the answer! Who needs MS Access!

Bonus credit - not that I will ever do this but let's say to back this out, that is to remove (in the example) the chars "HID0000" you would maybe use Filter (_id; (Left(some_value)) to make HID00001234 and put it back to 1234? But you need to be able to determine the Position in {somevalue} of the last padded "0". This probably means a fairly sophisticated use of the Left Function in combination with the Position Function. What would you do to determine that position (count from the left until you get to ) when you might have HID00000012?

H

[doughemi]

How about

GetAsNumber(_id) ?

(Since numbers don't have leading zeros)

[comment]

GetAsNumber ( SerialID ) will work in this case, because the original text "HID00000000" contains only alpha characters and zeros.

[Hoib]

Back again - with another rookie mistake...

 

I'm back to my food pantry solution, struggling with Members and Households.  Many to one.  In my Members table I have _kf_household_id, which were plain digits and which Comment kindly showed me how to extend to HID0000001, HID0000002 and so forth with serialincrement.  In my Household table, I have _kp_household_id autoentered as "HID#######".   I had to import the Members from a previous .fp7.  But, I could not get the Household Members to show up in a portal on my Household layout referencing _kf_household_id in the Members table.  Many hours passed.  And so today I stumbled upon what I think the answer is.  Somehow in the Members table I have many records with _kf_household_id sitting as "HOU#######" and not "HID#######".  I don't know how this happened; I can't remember what I did.

 

So, point is now I have to change all those "HOU"s to "HID"s in order to be consistent accross all records.  I've tried some string replacements like create a found set, replace field contents with formula:  left(_kf_household_id, 3) = "HID" but of course that didn't work.

 

Who would care to share with me how to make the "HOU"s into "HID"s?  If I can get that repaired, maybe I can settle in on how to get all Members with _kf_household_id of "HID0000001" to show up.

 

H

[comment]

Try =

 

Substitute ( Yourfield ; "HOU" ; "HID" )

[Hoib]

Nahh!  ....Can't possibly be that simple...

 

 

 

...but it is...

 

 

 

Wish I knew all these commands, like you do.

 

Once again, thank you!

 

H

[comment]

Again, it is simple in this case, because we know there is only one instance of "HOU" in the string.

 

 

Wish I knew all these commands, like you do.

 

You should go over the functions in the reference section of the help, at least once. Script steps, too.