Format issues with FMPA 11 checkbox set exported to Excel

Posted on 2010-08-19
Last Modified: 2012-08-14
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?
Question by:MarkJulie
  • 4
  • 4
  • 2

Expert Comment

ID: 33479328
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

Author Comment

ID: 33479717
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.

Accepted Solution

ThomDroz earned 250 total points
ID: 33480307

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?

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.


Author Comment

ID: 33480737
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?




Expert Comment

ID: 33481868
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

Author Comment

ID: 33482140
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.

LVL 28

Assisted Solution

lesouef earned 250 total points
ID: 33482783
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...

Expert Comment

ID: 33483198

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!

LVL 28

Expert Comment

ID: 33487202
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.

Author Closing Comment

ID: 33487671
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.



Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question