Case / Count Calc based on value

[AlanP]

Good morning all,

 

I have to do an import of data into FMP, but the field we are 'matching' to is configured differently than FMP.

 

EmployeeID from the CSV file we are importing is a 6 character field, where the employee ID itself is actually 4 or 5 characters and any remaining empty values are prefixed with a 0.  Such as a 4 digit employee ID is shown as 001234 and a 5 digit employee ID is shown as 012345.

 

In our FMP database, the EmployeID is shown without any prefixed 0's.  So it is 1234 or 12345.

 

When importing, these don't match up.

 

I'd like to make a calculation in FMP called EmployeeIDConverted where if the EmployeeID in FMP is a 4 digits, it displays as 00xxxx (prefixed with two 0's) and if 5 digits, it displays as 0xxxxx (prefixed with one 0), so I can successfully match records for import.  I'm not exactly sure how to do this (tried a few ways, but didn't work).

 

Thank you in advance!

[eos]

Right ( "000000" & employeeID ; 6 )

 

Make sure to set the result type to text.

[AlanP]

Right ( "000000" & employeeID ; 6 )

 

Make sure to set the result type to text.

 

Thank you!!

[comment]

Have a look at the SerialIncrement() function.

[AlanP]

Have a look at the SerialIncrement() function.

Would that be better to use?  I'm not very familiar with that function.

[comment]

Would that be better to use?

 

IMHO, yes, for a number of reasons (none of them very significant, though). Still, it is the function designed specifically for the stated purpose. There is also a difference in how the 1,000,000th employee will be handled.

[AlanP]

IMHO, yes, for a number of reasons (none of them very significant, though). Still, it is the function designed specifically for the stated purpose. There is also a difference in how the 1,000,000th employee will be handled.

 

Ah ok.  Could you recommend what I should put for the calculation?  I'm not sure how to use the SerialIncrement() function.

[comment]

SerialIncrement ( "000000" ; EmployeeID )

BTW, earlier you said:

 

In our FMP database, the EmployeID is shown without any prefixed 0's.  So it is 1234 or 12345.

 

 

Why don't you simply strip the leading zeros from the imported values, instead of padding yours?