AnsweredAssumed Answered

Script to repurpose data from excel spreadsheet in fmp12

Question asked by CeriThomas on Jan 9, 2013
Latest reply on Jan 14, 2013 by 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   

Outcomes