6 Replies Latest reply on May 23, 2014 8:44 AM by philmodjunk

    Can I convert multiple fields into one?



      Can I convert multiple fields into one?


           Below screenshot is the source that I am about to import. 

           The column F ~ J indicated what types of shipment were made with a letter V.

           My question is that can I somehow convert these five fields into one draw-down field after importing?

           So on FileMakePro database will only have a field called "shipment type" which contains a draw-down menu with the choices of air shipment, ocean shipment, FedEx, UPS, and Post? 


        • 1. Re: Can I convert multiple fields into one?

               Create a column K with a calculation and import that column instead of F-J. You could also calculate it in FM after import.

          • 2. Re: Can I convert multiple fields into one?

                 If you want to do it after import:

                 Create a new Calculation field:

                 Case (
                     not ( IsEmpty ( AirShipment ) ) ;
                     "Air Shipment"
                     not ( IsEmpty ( OceanShipment) ) ;
                     "Ocean Shipment"
                     not ( IsEmpty ( UPS ) ) ;
                     not ( IsEmpty ( FedEx ) ) ;
                          not ( IsEmpty ( Post ) ) ;
                 You should change the text in Green with the names of the fields you imported into.
                 Then I would create a new text field called ShipmentType
                 Then in table view replace the contents of the new Text field with the contents of the Calculation field.
            •Click in the new ShipmentType field
            •Records → Replace Field Contents → Replace with Calculated Result...
            •In the Specify Calculation window... Double click your new Calculation field in the list to add it to the big text box then click OK 
                 Then create a Value List with these options.
            Air Shipment
            Ocean Shipment
                 Back in Form view, add a new field to your table and change the Control Style from edit box to pop-up menu in the Inspector, then select the values from the new Value List you created.
            • 3. Re: Can I convert multiple fields into one?

                   You can also do this during the import.

                   Your field can be defined with an auto-enter calculation that combines the data from Fields F-J. Then import  with Auto-enter Field options enabled.

                   List ( If ( Not IsEmpty ( Air Shipment ) ; "Air Shipment" ) ; IF ( Not IsEmpty ( Ocean Shipment ) "Ocean Shipment" ) ; If ( // and so forth....) ... )

                   After importing, if this is a one time import, you can delete the extra fields.

              • 4. Re: Can I convert multiple fields into one?
                Alternatively, if you're more comfortable with finds, and imports (than you are with calculations), you could run similar imports 5 separate times. Each one would import only a subset of records, and would only have 1 field mapped differently than the others.
                • 5. Re: Can I convert multiple fields into one?

                  RE: hbrendel

                       I followed you instruction to create a calculation field and replace it after import, and it works. I like it. Thank you~~ You really save my day. 


                  RE: PhilModJunk

                       I would also want to figure out how your method works because it won't be a one time import. I will continue import the same documents when my co-worker send me the updated version. 

                       However, I do not know how to define a filed with an auto-enter calculation that combines the data from Fields f-J. Can you provide me more details? 

                       Also, about the calculation that you listed, what does If ( // and so forth....)...) mean? 

                  • 6. Re: Can I convert multiple fields into one?

                         anything after a // in a calculation field is a comment and ignored by FileMaker when evaluating.

                         And so forth.. is my comment to continue adding more lines of this calculation in the same pattern shown when you actually define this calculation.

                         I've shown the code for the first two columns of imported data (Air Shipment and Ocean Shipment) and left it up to you to finish the calculation by adding terms for the remaining 3 columns.

                         And my calculation makes an assumption that is probably not needed for your data. It is set up to work even if you have a V in multiple columns of the same row. If there is never more than one of the 5 columns that will have text, you might find it simpler to use the calculation supplied by Phil_1986 as your auto-enter calculation.

                         To define an auto-enter calculation, you define a data field (text in this case) and open the field options button (double click the field definition or click the Options button in Manage | Database | Fields.) THen you click the auto-enter tab, select the calculation option and enter the calculation into the specify calculation dialog that then appears.

                         When you import your records or set up the script to import records, there's a small odd dialog box that pops up just after you click the "import" button that asks if you want to split repetitions into repeating fields and/or enable auto-enter options. Make sure to select the check box for enabling auto-enter options.