Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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?

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

808 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