Format issues with FMPA 11 checkbox set exported to Excel

I have addressed this issue at a lower level in 26354304 and on another forum who suggested using VBScript. I have upgraded to FMPA 11 and have Excel 2003, 2007, and 2008 (Mac and PC). The big question is how to export several FM fields composed of multiple checkboxes each to Excel format with semicolon delimiters. FM data is numeric. When it gets to Excel, it appears in one of two ways. Either the numbers concatenate in a cell with box (LF or CR) delimiters, or they concatenate with no delimiters. If the first, when I click on the cell, the box delimiters disappear, and the numbers show up as a vertical column (as I would expect with CR or LF delimiters). There doesn't seem to be a Find and Replace ability in Excel to remedy the situation (I don't know VB). Using csv format on the export or Excel side destroys other unrelated formatting of text fields with decimals that get treated as numbers by Excel.

My logic says I should manipulate on the frontend with FM. One option would be to view the FM list, write a script and/or script trigger to concatenate with semicolon delimiters, save the result as a variable or into another field, and export the new field as a result to Excel.

What would be the easiest approach to script and debug in FMPA 11 (with or without AppleScript), or should I go from the Excel end?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi MarkJulie

There are several approaches to your problem.

I am a fan of simplicity 1st, so have you tried this.

Create one field that is a calculation field of all the fields you want then force the delimiter you want.

NewCalcField= Field1 & “;” & Field4 & “:” Field 6

Tell the calculation to be “text”

Then just export the one field

Hope that helps~!

Tom Droz
MarkJulieAuthor Commented:
The principle is where I was headed, but the process is a little harder since it is not multiple fields I am concatenating but a checkbox set for one field where multiple values can be selected in one field. Hence, I need to concatenate values that FM treats as a list within one field. Hope that helps guide your advice.

If Filemaker is inserting a "pargraph" return, then you can use the Subsittue command replacing that with a " "

If the filed Has,
You would return "Red Yellow Blue":

If that is not what you are attempting, can you give a sample of the data?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

MarkJulieAuthor Commented:
I can logically craft a solution from my elementary training in Java and C++, but the FM syntax or functions may not be what I expect. Please correct my algorithm.

Is a multiple checkbox field in FMPA (e.g., favorite_foods) considered a listOfValues?

If so, does ValueCount(favorite_foods) return 7 if my value list has {Mexican, Chinese, Japanese, Indian, Italian, Korean, Thai}?

If ValueCount(favorite_foods) varies with each record, can I script a loop from iterator=1 to ValueCount-1and concatenate food_list=food_list & ";" & GetValue(favorite_foods;valueNumber) while valueNumber < ValueCount? At valueNumber = ValueCount, I would skip the semicolon after the last value gets concatenated.

Am I on track? (This is not a school assignment.) The real data is protected heatlth care demographics where multiple medications, diseases, symptoms, or ethnicity are common. Here is my pseudocode not yet ready for FM:

food_list = "";
if ValueCount(favorite_foods) = 0
     food_list = "" ;
else food_list = GetValue (favorite_foods; 1);
if ValueCount > 1
    for (valueNumber=2; valueNumber < ValueCount; valueNumber ++)
      { food_list = food_list & ";" & GetValue(favorite_foods; valueNumber) }
food_listExport = food_list;

In FM, does 'Loop' function as both 'for' and 'while'? Do I use 'Exit Loop If' and exit for ValueCount = valueNumber? Is there a more efficient command in FM?



HI again

It would be helpful to me if you could give some samples of what is in the fields and what you would like the output to be
MarkJulieAuthor Commented:
Table: MedicalHistory
Field1: Mumeric codes (range 1-10)
Field2: Descriptive names of same (e.g., 1=heart attack, 2=diabetes, 3=cholesterol, 4=cancer, 5=low thyroid, 6=pregnant, 7=depression, 8=stroke, 9=fibromyalgia, 10=pneumonia)
Fields are sorted on Field1.
Field2 is displayed but not sorted or stored.
MedicalHistory is displayed on my FM db as a checkbox set allowing multiple choices.
Record1 {1, 2, 3, 6}
Record2 {2, 4, 6, 9}
Record3 {7, 8, 9}
Record4 {2, 4, 6, 8}
Record5 {7, 10, 1, 3}

When the five records export to Excel, each array fills a cell with the column header MedicalHistory (the name of my FM field). The delimiter in Excel is a CR between each value in the same cell.

I need the five cells to individually display as (without the quotes):

I have similar fields in the table for medications at home, initial symptoms upon arrival, and discharge location. All are numeric fields that export with either a CR delimiter or no delimiter and have more that one entry per field name.

I have no liberty to rename the fields or the delimiter. This data is collected in FM, exported to Excel, and uploaded to a national database with strict formatting requirements (including the semicolon delimiter).

I am happy to explain further if this is inadequate.

check boxes generate text content, each box checked generates the text label + fm return sign (¶)
so assuming the choice is  1 2 3 4 5 and you have 1 and 3 on: the text field is "1¶3¶".
so the easiest is to convert this with a simple substitute from ¶ to the separator you like into another field.
you could loop (fm has basic loops, you have to count yourself to exit except when looping on records) also for the amount of values, but replacing is enough here.
about using csv export format, this is still one of the best; numbers loosing their leading "0" is excel's fault! the possible workaround is to add a ' (single quote) to all numbers fields before exporting them (yes that means create an extra text field to store it).
you then have to search/replace them in excel...

Iif you can make it simple ie

Record 1
Field one has (1,2,3)
Field 2 has (2,5)
Output desired (????)

Record 2
Field1 has (2,9)
Field2 has (2,7,9)
Output desired (????)

I think that will add some clarity
Based on your description the subsitue command and the calaculation still sounds like that would work.  Perhaps an example like above will change my mind.

Good Luck!

I think he wants to recuperate the checkboxes in excel, but as far I know (I know excel badly), a checkbox is linked to a true/false field, so unless this is converted to several fields, the only way is to use csv and export check and unckecked values.
for instance; if the choice is 1 to 10 and only 1,2,5,and 7 are checked, you'd export
true;true;false;false;true;false;true;false;false; and use the same separator as csv's in his country to create pseudo field during export.
but let him answer first.
MarkJulieAuthor Commented:
The simplest solution was suggested by both by Tom (first) and then in slightly more detail at my level by lesouef. Thus, I split the points evenly. Eliminating the FM return character via SUBSTITUTE worked well. That prevents the conversion in Excel from FM return character to Windows CR character and creates an usable text string in the Excel cell.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.