1 2 Previous Next 18 Replies Latest reply on Jan 31, 2013 9:25 AM by philmodjunk

    Comma Separated Results via CSV Export?

    djanke

      Title

      Comma Separated Results via CSV Export?

      Post

           Hi,

           I am using the export function to export data to a csv or excel file.  The output is giving me a carriage return in the field for any fields that used a checkbox set with multiple selections.  Is there a way to export these that they would be comma separated?  I'm new to filemaker so please excuse the ignorance.

            

            

        • 1. Re: Comma Separated Results via CSV Export?
          philmodjunk

               Yes and no. You can export from a calculation field that replaces the returns with commas, but the entire string will be quoted in order to make sure that the commas inside this field are not treated as field delimitters.

               This calculation will take the contents of your checkbox field and return a comma separated list:

               Substitute ( YourCheckBoxField ; ¶ ; ", " )

          • 2. Re: Comma Separated Results via CSV Export?
            djanke

                 I put this the field under manage database, options, validated by calculation, but when I export I still have the same result as before.  Is this the correct place to put the calculation? 

            • 3. Re: Comma Separated Results via CSV Export?
              philmodjunk

                   But you export the cacluation field instead of the original field.

                   And I'd use a field of type calculation for this, not an auto-entered calculation. See: Updating values in auto-enter calc fields without using Replace Field Contents to see why.

                   A validation calculation isn't what you want here at all as it is supposed to control what data entered into the field is accepted as valid.

              • 4. Re: Comma Separated Results via CSV Export?
                djanke

                     No sure what you mean. Sorry, but very new at this. Where would I enter this? 

                • 5. Re: Comma Separated Results via CSV Export?
                  djanke

                       When I use change field type to calculation I can no longer check the check boxes. 

                  • 6. Re: Comma Separated Results via CSV Export?
                    philmodjunk

                         Open manage | Database | Fields

                         Select the table where your check box field is defined from the drop down list of tables

                         Enter the name of a new field in the text box at the bottom.

                         Select "calculation" as the field type.

                         Click Create.

                         Enter your substitute calculation.

                         Click OK

                    • 7. Re: Comma Separated Results via CSV Export?
                      djanke

                           So you are creating another field to store the calculated value which you would use in the export.  When I tried this I am getting double in the field on the form view and a zero on the csv exported file.

                            

                            

                      • 8. Re: Comma Separated Results via CSV Export?
                        djanke

                             Phil,

                             I created a new record and it seems to be working fine.  Thank you for you help, it's greatly appreciated. 

                             -Don

                        • 9. Re: Comma Separated Results via CSV Export?
                          djanke

                               I have successfully exported the data to an excel file, but there is one strange thing happening.  The fields that have the check box values separated by the commas are not in alphabetical order, or in the order the check boxes are in in file maker.  Any reason this would happen? 

                          • 10. Re: Comma Separated Results via CSV Export?
                            philmodjunk

                                 That's the way the check box group interface works. If you place a copy of your check box field next to the original but reformatted as an edit box, you can actually watch what happens when you click different check boxes in the original copy of the field. The values are stored in the order that the user clicked the check boxes, not the order displayed in the check box formatted field.

                                 If you need the values to appear in a sorted order, you'll need to put in some additional work. One way is to use this check box field as a match field to a related table where you have one record for each value with the check box value stored in a text field. If you then specify a sort order on this relationship, List ( RelatedTable::ValueField ) will list the values in the sort order specified in the relationship. Here's a step by step:

                                 1) Say you have a value list with three values: Apple; Pear; Peach

                                 2) Define a new table, ValueTable with three records and one field named "Fruit". Enter the three values into "Fruit" in different records of this table.

                                 3) define this relationship:

                                 YourTable::CheckBoxField = ValueTable::Fruit

                                 In Manage | Database | Relationships, double click the relationship line and specify a descending sort order for Fruit in valueTable.

                                 4) Define your calculation field as:

                                 Substitute ( List ( valueTable::Fruit ) ; ¶ ; ", " )

                                 5) And if your value list for this check box is a custom value list, you can modify it to be a "Use values from a field" value list and specify Valuetable as the source of values for your check boxes.

                            • 11. Re: Comma Separated Results via CSV Export?
                              djanke

                                   Thank you this worked.yesTwo questions in the calculation formula, what does list do?  And what if I have other check boxes I need to do the same thing.  Say for example pasta, would I have to create another value table or would I go to the value table I create and add a field pasta for the different types of pasta? 

                              • 12. Re: Comma Separated Results via CSV Export?
                                philmodjunk

                                     I suggest reading up on the List function in FileMaker Help to get more detail. Basically, it's similar to the aggregate functions such as sum, count, average... where it produces a value from an "aggregate" of fields--either literally listed within the parenthesis or from, as in this case, a related set of records. It produces a set of return separated values identical in form to your check box group formatted field, but with this trick we can sort the values as we want. Another feature of List is that any empty values are automatically omitted from the resulting list so that can be very useful in other contexts.

                                     Fields that contain return separated values function as a kind of "or" relationship when used as match fields in a relationship. It matches records in the other table if any one of the listed values matches the value in the other table's match field. This means a return separated list of values can function as a kind of "hidden join table" for a many to many relationship. I don't recommend it as a substitute for the join table except in special circumstances such as this one where the resulting loss in flexibility (when compared to a join table) is not an issue.

                                     I would use separate tables. If you used one table, deleting a record from the table deletes one value from both of your value lists.

                                • 13. Re: Comma Separated Results via CSV Export?
                                  djanke

                                       What do you mean by substitue of "join table"?  Are you saying this should create these secondary tables for values if sorting of the data is imporant to you, otherwise use the vaule list like I first set up?

                                  • 14. Re: Comma Separated Results via CSV Export?
                                    philmodjunk

                                         The key detail is that you DO NOT need a join table in your situation.

                                         I'm just providing some "database theory" on how the relationships work when you use a return separated list of values as a match field in FIleMaker. A join table is something created in a typical relational database and also in FileMaker to implement a "many to many" relationship like you have here. But FileMaker, unlike most relational databases, also enables you to use a return separated list of values in place of that additional table. This usually entails a loss of flexibility in working with those "join" table values, but is not an issue in your case.

                                         Usiing tables of Values for your value lists, which is not a join table, can be useful for a number of different tasks. It can make it easier for the general user, for example, to manage the values that appear in the value list, to name one example.

                                         But given the fact that check box and radio button formatted fields cannot automatically resize in browse mode to show varying numbers of values, custom value lists (where you type the values into Manage | Value Lists) is more commonly used for them..

                                    1 2 Previous Next