Solved

Format issues with FMPA 11 checkbox set exported to Excel

Posted on 2010-08-19
10
964 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
[X]
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
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

688 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