Solved

Format issues with FMPA 11 checkbox set exported to Excel

Posted on 2010-08-19
10
948 Views
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?
0
Comment
Question by:MarkJulie
  • 4
  • 4
  • 2
10 Comments
 
LVL 6

Expert Comment

by:ThomDroz
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.

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

Tell the calculation to be “text”

Then just export the one field

Hope that helps~!

Tom Droz
Droz-Consulting.com
0
 

Author Comment

by:MarkJulie
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.
0
 
LVL 6

Accepted Solution

by:
ThomDroz earned 250 total points
ID: 33480307
MarkJullie

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

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

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

Thanks
Tom
0
 

Author Comment

by:MarkJulie
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?

Thanks.


       


0
 
LVL 6

Expert Comment

by:ThomDroz
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
:)
Thanks!
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:MarkJulie
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):
"1;2;3;6"
"2;4;6;9"
"7;8;9"
"2;4;6;8"
"7;10;1;3"

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.

Mark
0
 
LVL 28

Assisted Solution

by:lesouef
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...
0
 
LVL 6

Expert Comment

by:ThomDroz
ID: 33483198
Mark

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!

Tom
0
 
LVL 28

Expert Comment

by:lesouef
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.
0
 

Author Closing Comment

by:MarkJulie
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.

Thanks,

Mark
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now