13 Replies Latest reply on May 10, 2012 6:46 AM by LSNOVER

    Excel Interface - Copy/Paste into repeating field

    suesaunders

      Hi there FMP Techies!

       

      I want my Business Planning app to be able to take a line item in Excel - say Sales from Jan through Dec for 2 years = 24 cells. Copy this line item to the clipboard. I only want to transfer this particular line item not the whole Excel spreadsheet.

       

      Then go into the FMP12 app and paste into my 24 element repeating field. Easy! Except that all of the Excel data is pasted into the first element of the repeating field. That is ok I suppose. Except I have to write a fancy script to decode the string and put each Excel element into the corresponding place in my repeating field. Can be done but tricky. The Excel data has fuzzy delimiting - like spaces or maybe tabs - hard to tell. It will need to be a trial and error exercise, I fear.

       

      Question: Has anyone done this before? It is a bit like a FMP "paste special".

       

      The repeating field is fundamental to my app design.

       

      Best regards

      Sue

        • 1. Re: Excel Interface - Copy/Paste into repeating field
          MicheleOlson

          Sue,

           

          Are you *really, really, really sure* you must use a repeating field? That type of field is usually not a good choice. Can you describe why you need a repeating field vs a table with a record for each repetition?

           

          Just curious,

           

          Michele

          • 2. Re: Excel Interface - Copy/Paste into repeating field
            beverly

            Sue, the cells should have a TAB (horizontal tab, ASCII 9) between them when copied from Excel. The repeats should have ASCII 29 between them for FileMaker. That is why you get the entire row  pasted into the first repeat (tab-delimited).

             

            I don't know if you can create a cell in Excel that would give you the correct separator. And I dont know if a copy/paste would work this way (into a repeating field).

             

            HTH,

            Beverly Voth from my iPhone4

            • 3. Re: Excel Interface - Copy/Paste into repeating field
              MicheleOlson

              Should have, but... they don't if the version used is 12.

               

              Paste into 11 and the separation between a row of cells is obvious; paste into 12 and data all runs together.

               

              If you could transpose the data into a column before pasting into either version of FileMaker, the task is much simpler.

               

               

              Here is the script I used to move the data pasted into another field into the repeating field. I think you'll need to paste into a single field and then move into the repeating field.

               

              SCRIPT: Move to Repeats

              Set Variable [ $valueCount; Value:ValueCount ( test3::PasteField ) ]

              Set Variable [ $valueSet; Value:1 ]
              Loop

                   Set Field [ test::RepeatField[$valueSet]; MiddleValues ( test::PasteField ; $valueSet ; 1 )

                   Set Variable [ $valueSet; Value:$valueSet + 1 ]

                Exit Loop If [ $valueSet>$valueCount ]

              End Loop

               

               

              I also set the repeating field to remove paragraph returns at the end of each line with an auto-enter calc:  Substitute(RepeatField; "¶";"")

               

              Michele

              • 4. Re: Excel Interface - Copy/Paste into repeating field
                suesaunders

                Dear Michele

                 

                This is great.  I will try it in the next few days and post whether it was successful.  That is a great help.

                 

                About my app.  I first came to know computers way, way back at University (mid 60s) where I was introduced to Fortran.  This was in the context of mathematical and scientific applications where there were a lot of computations.  In those days data was housed in "arrays" and one used looping structures to do computations within the arrays.  My app uses a small amount of data but many, many computations.  It is easy to work with "arrays" in this instance.  Filemaker gives me the best of both worlds.  It handles the array structure happily but has the added bonus of wonderful screen layouts that make the app look professional and up to date.

                 

                I attended one of your courses years ago in Perth, Australia.  It was circa 1998 or 1999.  I have improved a lot since those days although writing FMP apps has not been my profession. 

                 

                The very best and thanks a lot!

                Sue

                • 5. Re: Excel Interface - Copy/Paste into repeating field
                  MicheleOlson

                  Thanks for the reply, Sue and the reminder about the class.  I loved Perth and remember it fondly. Are you still in that part of Oz?

                   

                  Glad I could give you a hint in another direction. I'm happy to post the sample file to you if you'd like, but you may want to work it out on your own. I enjoyed the challenge of working through the process.

                   

                  Speaking of arrays, you might want to look into using variables. I have not used them a lot in this fashion, but many developers speak of using them - since arrays can have repetitions - to temporarily manage data. Don't know if they would be useful in your application, but perhaps you may find them so in the future. Variable repetitions are written: $var, $var[2], $var[3]. etc. There is basically no limit to the # of repetitions. Just a thought.

                   

                  Best,

                   

                  Michele

                  • 6. Re: Excel Interface - Copy/Paste into repeating field
                    suesaunders

                    Dear Michele

                     

                    There are 3 things.  1. I presently live in Melboure - right in the CBD.  2. I already use variable repetitions.  FM is very clever.

                     

                    3.  The bad news.  I tried your method in FMP12 but it did not work.  The problem is that the function ValueCount(Test::PasteField) returns the value 1.  The long string of values pasted into the field does not appear to have any delimiting at all.  Key to your method is being able to identify the separate values in the Excel string.

                     

                    This is the content of the paste field.  It is just one long string. 

                    $2,560.00$2,310.00$2,692.50$2,595.00$1,795.00$3,492.20$4,761.58$3,992.15$6,092.10$5,792.05$5,812.00$7,644.65$7,623.40$7,850.38$10,569.08$11,681.95$10,377.63$10,540.78

                     

                    If I paste the same data into this HTML page, it has delimiting.

                     

                    $2,560.00$2,310.00$2,692.50$2,595.00$1,795.00$3,492.20$4,761.58$3,992.15$6,092.10$5,792.05$5,812.00$7,644.65$7,623.40$7,850.38$10,569.08$11,681.95$10,377.63$10,540.78

                     

                    There must be a way around this?

                     

                    I tried different field definitions for the paste field.  Number and container.  Neither gave me any way to distinguish between the numbers.

                     

                    I could write a script to use the $ as a delimiter but I cannot be sure that the user would format their spreadsheet as currency.

                     

                    Do you have any further ideas?

                     

                    Cheers

                    Sue

                    -------------------

                    • 7. Re: Excel Interface - Copy/Paste into repeating field
                      suesaunders

                      Hi Michele

                       

                      Success.  The trick was to use WordCount not ValueCount and to follow your suggestion of using a variable to collect the data.  Wonderful!

                       

                      Here is the script.

                       

                      Paste from Excel.png

                      I used the repeating variable to capture the data and then I could place it in the correct place in the current record.  Great!

                       

                      The User does not have to know about the PasteField.  All they do is hit the paste button and presto the values appear as they should in their forecast.

                       

                      Thanks so much for the help.

                       

                      Best regards

                      Sue

                      • 8. Re: Excel Interface - Copy/Paste into repeating field
                        MicheleOlson

                        Hi Sue,

                         

                        Good job! Looking at your data string I can see the problem with values.

                         

                        In my example, I had transposed the data from a row of cells into a column of cells. All of this was done in Excel, then the column was pasted into the first field. This worked because the delimiter then became a paragraph return, thus my use of ValueCount.

                         

                        When testing in FM12 that was the only way I could get any delimiter. The TAB or ASCII9 as Bev suggested, was just not read in 12.

                         

                        Not knowing what your data was, I didn't use word count. My test data had several words in the cell, so I went with values.

                         

                        You might have been able to use the dollar sign as a delimiter as well, but that would have been much more work I think.

                         

                        Melbourne CBD? My son and his family live in Bentleigh. I'll send an email before our next trip over. Perhaps we can meet up for a coffee?

                         

                        Best,

                         

                        Michele

                        • 9. Re: Excel Interface - Copy/Paste into repeating field
                          beverly

                          Interesting Michele. I can copy a cell in Excel and paste into a field in FM12. The tabs are definitely there. Perhaps it's a tab-stop setting or two on the field that makes the field appear as  NOT delimited?

                           

                          Choose "Ruler" from the "View" menu, click into the field and you should see the tab stops. If there are any, they can be moved. If there are none, they can be set here.

                           

                          Beverly

                          • 10. Re: Excel Interface - Copy/Paste into repeating field
                            MicheleOlson

                            Agreed. It is interesting.

                             

                            I did turn on the "Ruler" from the "View" menu. There are no tab stops that appear on the ruler with the pasted text selected.

                             

                            I added tab stops to the ruler, but they have no effect on the pasted text.

                             

                            At first I thought perhaps it was the length of the text in each cell. I had just opened a handy SS, selected 4 cells in a row and copied.

                             

                            I tested again by adding 4 numbers in a set of 4 cells in a single row. Same thing.

                             

                            Then tried with four words in a set of 4 cells in a single row. Same.

                             

                            Here are the four numbers from Excel:

                            1234

                             

                            which pastes into here as 4 cells as it should.

                             

                            Here is what pastes into FM 12:

                            1234

                             

                             

                            Same for words - from Excel:

                            ONETWOTHREEFOUR

                             

                            what goes into FM 12:

                            ONETWOTHREEFOUR

                             

                            I don't know what is causing the difference.

                            Mac OS X 10.7.3, Excel for Mac 2011, v. 14.2.1, FMPA 12.0v1

                             

                            The only other thing I notice is the copy from Excel to FM is quick. The copy from FM to this browser[Safari] window reply is s-l-o-w.

                             

                             

                             

                            Whadayathink?

                            • 11. Re: Excel Interface - Copy/Paste into repeating field
                              jormond

                              I get the same thing as you Michele.  I would assume it's a bug of sorts...since in the calculation dialog, the tabs remain in place.  And especially if Bev is seeing them remain when she pastes from excel.

                               

                              I use the EDI functionality of Excel a lot for stuff, so things not pulling into FM12 correctly is a source of frustration.  Hopefully FMI will get that worked out with with first rev release.

                              • 12. Re: Excel Interface - Copy/Paste into repeating field
                                suesaunders

                                Dear Michele

                                 

                                Firstly, it would be brilliant to meet up when you journey to Melbourne.    Please send email and we can arrange that coffee.  We can talk FM and scripts and all that. 

                                 

                                Secondly.  My script only works because the $ signs are there.  It is a bit clunky but I have to tell my customers that in order to use the Excel interface facility, they need to format their Excel data as "currency".  Anyway it is a great step forward.  It is very necessary for my app.  Saves my users a lot of time if the work that they have done in Excel can be transferred to my app.

                                 

                                For the record.  Numbers in Excel horizontally does not work.  Numbers vertically in Excel does work with my script.

                                Characters horizontally and vertically in Excel does not work. Not sure why this is so when I an using "words" and text fields.

                                 

                                I agree with Joshua that really FM should keep the delimiters when Copy from Excel, Paste into FM is used. I could not "see" the delimiters as Beverly suggested.

                                 

                                Best wishes to all

                                Sue

                                • 13. Re: Excel Interface - Copy/Paste into repeating field
                                  LSNOVER

                                  There is nothing inherently "WRONG" with repeating fields.  They are not evil.   

                                   

                                  They have some legitimate uses and in certain circumstances provide superior performance to breaking values out into seperate tables with Parent Child relationships. 

                                   

                                  Like most good things in life, they can be abused, but that should not disuade someone from using them.

                                   

                                  Cheers!

                                  Lee