1 2 Previous Next 15 Replies Latest reply on Jan 14, 2013 1:32 PM by CeriThomas

    Script to repurpose data from excel spreadsheet in fmp12

    CeriThomas

      Title

      Script to repurpose data from excel spreadsheet in fmp12

      Post

           I have a FM database that has an iphone compatible layout containing : item name, description, first power supply unit, first power supply unit outlet, second… etc with a total of three power supplies and outlets. 

           The information is populated using an excel spreadsheet with a combination of 12 power supply units with 24 outlets per supply. Each power supply has an ip address which doesn't change. pdu1 is 10.16.0.11, pdu2 is 10.16.0.12 etc the Excel sheet has each pdu as its own column and then is updated with the outlet number under for an item in the correct pdu column eg pdu6 outlet 23 would just have 23 in the column labelled pdu6.

           The filemaker database needs to populate fields First pdu ip, first pdu outlet, second pdu ip, second pdu outlet etc automatically. There is only one unit that has a third pdu and that is a seperate list item so I've used an if statement to match that.

           I had decided to use a Case test to populate a variable based on whether a field was empty or not:

            

           
                Case ( 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 1 ) ; "10.16.0.11"; 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 2 ) ; "10.16.0.12"; 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 3 ) ; "10.16.0.13"; 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 4 ) ; "10.16.0.14"; 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 5 ) ; "10.16.0.15"; 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 6 ) ; "10.16.0.16"; 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 7 ) ; "10.16.0.17"; 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 8 ) ; "10.16.0.18"; 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 9 ) ; "10.16.0.19"; 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 10 ) ; "10.16.0.20"; 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 11 ) ; "10.16.0.21"; 
           
                not IsEmpty ( _PDU layout_current.xls_ PDU layout::PDU 12 ) ; "10.16.0.22"; 
           
                )
           This works great for First pdu and it's outlets and for the third pdu in it's own branch but the case statement continues regardless of whether the value has been filled or not so the valuse for second pdu and outlet always matches first pdu and outlet even though they are different.
            
           Thinking about some form of Choose() test [or List() for the outlets maybe] but am at a bit of a loss.
            
           Any gurus care to step in?
            
           Thanks for your help.
            
           Ceri
            
           Current full script: https://dl.dropbox.com/u/230576/PDU%20Layout.pdf   

        • 1. Re: Script to repurpose data from excel spreadsheet in fmp12
          philmodjunk

               I suggest you consider getting that spreadsheet data restructured (can be done after import into FileMaker) such that each PDU is a separate related record. This will take a bit of scripting to move the data from a table structured to receive the imported data from the spread sheet, but the payoff is data that is much more flexible to work with. On particular advantage to you is that a much simpler relationship can then be used to reference data from a related table instead of using a calculation like you have here and which can be updated to use different values without having to redesign a calculation.

          • 2. Re: Script to repurpose data from excel spreadsheet in fmp12
            CeriThomas

                 Thanks Phil. Any guidance about where to start on the restructuring? I have a separate table that stores the PDU number and IP address should I consider using that as some form of link to the data?

                 Much appreciated.

                 Ceri

            • 3. Re: Script to repurpose data from excel spreadsheet in fmp12
              philmodjunk

                   What does the data look like for one row of data in the Excel spreadsheet? I am having difficulty using your original post to picture that.

              • 4. Re: Script to repurpose data from excel spreadsheet in fmp12
                CeriThomas

                     Here you go

                     Thanks for your help.

                • 5. Re: Script to repurpose data from excel spreadsheet in fmp12
                  philmodjunk

                       Keep in mind that I don't have the "big picture" behind what you are doing here, but it appears your end result should look like:

                       Items-----<PDUs>-----IPs

                       Items::_pkItemID = PDUs::_fkITemID
                       IPs::__pkIPID = PDUs::_fkIPID

                       Thus it would seem that you would have one table, IPs with exactly 12 records, each with an IP address, One Table, Items, with the Item field plus a primary key field and a third table, PDUs that has one record for each cell under the column headings PDU 1 through PDU 12 that contains any data. A record in this last table would have at least three fields:

                       PDUname (text) : stores one of the 12 PDU column headers, PDU 1, PDU 2, ... or PDU 12.
                       _kfIPID (number) : links to appropriate IPs record to make the correct IP address accessible
                       _fkItemID : links to the appropriate Items record to make the Item name (and any other data about that specific item) accessible.
                       Value (number) this holds the actual numbers shown, 21, 21 or 10 in your example screen. Empty cells will likely not need any record in this table.

                       Before I try sketching a script that would loop through the records of a temporary import table, does this make sense to you?

                       Please note that even after restructuring the data this way, it will still be possible to set up a layout that looks exactly like your spread sheet should that be desirable.

                  • 6. Re: Script to repurpose data from excel spreadsheet in fmp12
                    CeriThomas

                         At first glance that seems exactly like something I'd need. Could you give me a day or two to go over your post fully so I can be sure? This sn't my my main project right now and other stuff has to be priority but definitely interested in getting this done ASAP.

                          

                         All help is much appreciated, thank you.

                         C

                    • 7. Re: Script to repurpose data from excel spreadsheet in fmp12
                      CeriThomas

                           Phil,

                           If you could sketch a script like that, that would be great. It seems like you have a handle on the issue. You are correct about the empty cells not needing an entry in the Item table too.

                           Really appreciate the help. 

                           Ceri

                      • 8. Re: Script to repurpose data from excel spreadsheet in fmp12
                        philmodjunk

                             First step is Import Records of course. Import records and do NOT select the "First row is field names" option so that you get all of the data. Use text fields for every column in the spreadsheet when setting up this table. This script picks up immediately after importing the records. you should be on a layout based on this table (ImportTable) and all fields should be present on the layout as shown in your example with the default tab order. The first field must be named "Item" the other field names can be anything:

                             #Load Data into the IPs table
                             Go to Next Field  ---> focus should now be in second field of first row of data "PDU 1"
                             Loop
                                Go To Record/Request/Page [First]
                                Set Variable [$PDUname ; value: Get ( ActiveFieldContents ) ]
                                Go to Record/Request/Page [No dialog ; 2 ]---> This is the record number by calculation option.
                                Set Variable [$IP ; value: Get ( ActiveFieldContents )
                                Go To Layout ["IPs" (IPS)]
                                New Record/Request
                                Set Field [IPs::IP ; $IP]
                                Set Field [IPs::PDUname ; $PDUname ]
                                Go To Layout [Original Layout]
                                Go To Next Field
                                Exit Loop If [Get ( activeFieldname ) = "Item"]--->you've looped through the tab order until you've returned to the first field in the tab order.
                             End Loop
                             #Load Items and PDU tables
                             Go to Record/Request/Page [no dialog ; 3 ]
                             Loop
                                #Create New Item Record
                                Set Variable [$ItemName ; Value: ImportTable::Item]
                                Go To Layout ["Items" (Items)]
                                New Record/Request
                                Set Variable [$Item ; value: Items::__pkItemID]
                                Go to Layout [original layout]
                                #Create needed PDUs records
                                Go To Next Field ---> put focus in first PDU column
                                Set Variable [$K ; value: 1 ]
                                Loop
                                   Set Variable [$PDUvalue ; value: Get ( ActiveFieldContents ) ]
                                   If [ Not Isempty ( $PDUvalue ) //there is data in the cell]
                                      Go To Layout ["PDUs" (PDUs)]
                                      New Record/Request
                                      Set Field [PDUs::PDUvalue ; $PDUvalue]
                                      Set Field [PDUs::_fkIPID ; $K]
                                      Set Field [PDUs::_fkITemID ; $Item]
                                      Go to Layout [original layout]
                                   End If
                                   Go To Next Field
                                   Exit Loop if [ Get ( ActiveFieldName ) = "Item"]
                                   Set Variable [$K ; value: $K + 1 }
                                End Loop
                                Go To Record/Request/Page [Next ; Exit After Last]
                             End Loop

                             Notes: I am basing this on the two __pk fields being defined to auto-enter a serial number "on creation" in field options for these two fields. I am also assuming that the next serial value setting for __pkIPID to be 1 at the time this script is run. If it is not--perhaps you need to run this import periodicatlly to add more data, you'll need to modify this script to set $K to the correct initial value each time that you loop through the columns of PDU values.

                             Also note that I have not tested this script in any way. A newly created script of this length is very likely to have at least one glitch somewheres. If you have FileMaker advanced, you may want to test it the first time around by using the script debugger to step through it one step at a time so that you can look for any issues that pop up.

                        • 9. Re: Script to repurpose data from excel spreadsheet in fmp12
                          CeriThomas

                               Phil,

                               Thanks for that. Having some issues.

                               Tables: http://cerithom.squarespace.com/storage/pdufiles/Tables.png

                               Script to populate IP table http://cerithom.squarespace.com/storage/pdufiles/IPScript.png

                               Resulting IP Table: http://cerithom.squarespace.com/storage/pdufiles/IPs_table.png

                               It seems to be cycling backwards through the fields so only grabs the first field name and contetns then cycles back to the empty item category. also have your script as written running which seems to completely skip the ip address row and put Data domain in as first item in the IP table with Value set as "item", hence modifications made.

                               I don't understand why it's cycling backwards and thus never exiting the loop.

                               Any thoughts?  

                          • 10. Re: Script to repurpose data from excel spreadsheet in fmp12
                            philmodjunk

                                 Darn, didn't remember that changing layouts also changes the field focus. Each time your script or mine changes back to the import layout the focus is lost and go to next field puts the focus into the first field in the tab order, Item.

                                 This is going to take a different scripted approach here to cycle through the fields in the column.

                                 It might work to use new window followed by go to layout to select the second layout. Then use Select Window instead of go to layout to return to the import layout as it will then remain in the original window. That might result in some annoying window flashes, but it should leave the focus in the import table layout undisturbed.

                                 I suppose there are several other options for modifying my original cut on this that don't require two windows, but if this is a once used or rarely used script, it may be less work just to add a few steps to use two windows instead of changing layouts in one window. (Don't try this with maximized windows on a Windows system as this resizes the original window in ways that may get confusing.)

                            • 11. Re: Script to repurpose data from excel spreadsheet in fmp12
                              CeriThomas

                                   Thanks Phil. 

                                   New windows works so I now have 4 windows to the application.

                                   wMain, wIP, wPDU, wItem

                                   wIP is open only while the first loop is running. For some reason if I'm able to get it to select the first PDU field it inputs either a blank value or 2 PDU 1's. If I start it at the item field it skips to PDU 1, goes back to item then tries to loop through, haven't managed to get it to create 12 complete and correct entries, 14 with PDU1, item,PDU1 allowing a duplicate entry then 2-12, driving me up the wall. 

                                   If I allow it to enter the duplicate value then the wItem seems to populate correctly up to the 4th item then just loops and loops until I quit it. Very bizarre. 

                                   Thanks for your help thus far do you have any guidance?

                                   Ceri

                              • 12. Re: Script to repurpose data from excel spreadsheet in fmp12
                                CeriThomas

                                     doh. needed to reattention first window before the IP loop. that's working now.

                                     On with the rest of the party. 

                                     C

                                • 13. Re: Script to repurpose data from excel spreadsheet in fmp12
                                  CeriThomas

                                       and it's working! couple of refocuses that I'd missed. Now to build the iphone layot and search etc and should be golden.

                                       Will post final solution once confirmed.

                                       Thank you so much. 

                                       C

                                  • 14. Re: Script to repurpose data from excel spreadsheet in fmp12
                                    philmodjunk

                                         Please note that my last suggestion was to enable you to tack a "band aid" onto my  less than optimum script in order to get it to work.

                                         There are ways to do this all from one layout in one window, but I didn't judge it worth the effort if this is for a one time import of data into your database. It may well be worth such a redesign if you have to import this data repeatedly.

                                    1 2 Previous Next