1 Reply Latest reply on Jul 2, 2010 3:02 PM by fitch

    Show 2 records in the same table and layout.



      Show 2 records in the same table and layout.


      Hi, I need your help :-)

      My database is connected via ODBC to a remote mysql server.

      I have a table for my customer database. The problem is that a customer can have more of an address (billing and shipping). When a customer add more than a address, automatically 'the website' add a record in this table with the same customer ID so I can find a duplicate record for one customer, each one with different address (one with billing address, one with shipping address).

      Now, my objective is don't show duplicate record (quite easy with a script link directly on the filemaker file) and show the shipping address (of the record that I omitted) in the same record/layout of the customer with same ID.

      The problem is that these record are on the same table....

      How can I show a field from omitted record in an another record whit the same ID in the same table? Is it possibile? Could you help me? I can't modify the table on the remote mysql server.... 

      If can help, the second record have a field with another creation date  and another field with a different ID.


      Customer Table

      ID (field - when a customer add the shipping address I can find 2 record with the same ID but the field 'address' has different value)

      Second ID (field - this ID is unique and different for all the records)

      customer name (field)

      address (field)

      creation date (field)

      etc etc....

      The script that I want use for omit duplicate record is:

      goto record [first]


      exit loop if [ get ( foundcount ) = 0 ] 

      if [ $id = id ] 

      omit record


      set variable [ $id ; id ] 

      go to record [ next , exit after last ] 

      end if 

      end loop