Hi.

I am looking for a way to combine fields into a single text string. eg a calculation to merge the first 5 characters of a last name field + the first 5 characters of a first name field + date of birth in YYYYMMDD format etc. This would all have to merge into one field and the number of characters merged into the field must be fixed.

The purpose of this is to submit medical billing records in machine readable ascii format as specified by the payer.

Any suggestions?

Thanks

What you want to do, is called Concatenate your fields. Off of the top of my head, this would be like this:

Left (LastName; 5) & Left(FirstName; 5) & Year(date of birth) & Month(date of birth) & Day(date of birth)

HTH

Lee

I couldn't spot too many inconsequent rules, so why not just go ahead with:

http://www.filemaker.com/help/FunctionsRef-366.html

and "&'s" for each bit.

--sd

To convert a date to YYYYMMDD (with leading zeros), you can do:

10^4 * Year ( date ) + 10^2 * Month ( date ) + Day ( date )

(assuming you don't have any dates before the year 1,000)

Good Catch, and nice calculation.

Lee

Thanks for the replies.

I will give these a try although I previously tried using "&" and it didn't work, but I'll give it a go again.

"Concatenate"--nice! I guess reverse Parsing is not a technical term.

I guess reverse Parsing is not a technical term.

Well, you made us look.

your calculation should now look like this. and your Result of the Calculation should be Text.

Left ( LastName ; 5 ) & Left ( FirstName ; 5 ) & 10^4 * Year ( DateofBirth ) + 10^2 * Month ( DateofBirth ) + Day ( DateofBirth )

Lee

Ok. It is working.I'm not sure what I did wrong before. Now the only issue is that, for example, the number of characters in the ascii string designated for last name, may be 20. If the last name is not 20 characters then the remaining characters in the string should be place holding spaces. I've been trying various calculations, but I'm stumped.

Append 20 spaces to the string, then use the Left() function to get the first 20 characters of the result.

I'm not sure what you mean by

If the last name is not 20 characters

Last Name = 5

First Name = 5

DoB = 8

Total Max 18

did you mean you want the Names to contain at least 5 characters, or the string to be 20, with leading 00s?

Lee

Sorry. Yes it was confusing. Forget the original example.

If the string allows for 20 characters for Last Name and all 20 characters must be present in the string even if the Last Name is less than 20 caharcters, then a 5 letter last name would require 15 spaces after it in the string.

Appending with extra spaces and then using the Left function works as was previously suggested.

Thanks

Perhaps you should consider exporting as XML instead. There is a fixed-width export stylesheet in the XML examples that are installed with the application.