2 Replies Latest reply on Sep 12, 2011 11:52 AM by philmodjunk

    Newbie here, help please



      Newbie here, help please


      I would appreciate some help with this situation. Thanks so much.


      Table - Lease List

      Key Field - LEASELISTID

      fields - True Owner Company, Lease Number, address, phone, square footage...


      Table - Lease List Verified


      other fields - True Owner Company, Lease Number, address, phone, square footage


      Table - Contacts

      Key Field - CONTACTID

      other fields - Company, address, phone, square footage


      I currently have a portal on the Contact layout that shows the records from the Lease List Verified table


      I import data from excel into the Lease List table and modify some of the data. Once I'm done modifying the data, I want to click a button "Verify" that looks up the field Lease Number in the Lease List Verified table. If the Lease Number exists, I want it to update the record with the data in the corresponding fields from the Lease List Layout. So it goes from Lease List to Lease List Verified by clicking the button. If the Lease Number doesn't exist, I want it to create a new record in the Lease List Verified table and input the data from the Lease List table. 

      Then, I want the same sort of functionality from the Lease List Verified table to the Contacts table. On the Lease List Verified layout, I want a button that will add certain fields from Lease List Verified to Contacts. For example, the field Lease List Verified::True Owner Company should create a new contact record and set the value of Contacts::Company to Lease List Verified::True Owner Company. 


      Basically, I'm importing data into Lease List, verifying the data, transfering it to Lease List Verified, then creating a contact (if one doesn't exist) from the data in the Lease List Verified table.


      New to filemaker and this is way above me. I'm about 435 pages through the missing manual and haven't gotten to the advanced stuff yet, which this clearly is and I need to get this going. PLEASE HELP. thanks.  


        • 1. Re: Newbie here, help please

          It seems that you have data entered in an Excel sheet.  Then you transfer it to a Filemker file and modify it, verify it, whatever.  Then you transfer it again to another identically-structured FM file.  I can't help feeling that there is a big potential for streamlining there.

          However to answer your question: it could be done several ways.  This way may be the easiest to follow and therefore the easiest to de-bug.

          In principle:

          Create a relationship between the Lease List Table and the Lease List Verified table, linked by Lease ID

          Find the list of Leases in the Lease List Table to be transferred.  Assuming that as the starting point for a script to run, do:


          Go to the Lease List Table (use the Go To Layout script step)

          Go to the first record.

          Start a Loop here

          Capture all the necessary field values that are to be transferred as $Variables

          Set Variable ($TrueOwnerCompany , LeaseList::TrueOwnerCompany )

          Set Variable ($Address , LeaseList::Address )


          Put an IF script step here

          Check if the record has a valid matching record in the Lease List Verified Table (by checking if the LeaseList::LeaseListVerified:LeaseID exists)

          IF it does exist, use the Go To Related Record script step to transfer you to the matching record in the Lease List Verified table


          If it does not exist, go to the Lease List Verified Table and use the New Record script step to create a new record

          End IF

          Then set the fields from the $Variables.  (This will re-set them in any existing record, or populate any new record)

          Go back to the Lease List table (Go to layout script step).

          Go To Record [Next, exit after last]

          End the loop here


          You can do something similar for the Contacts.

          Is that ok, or do you just think I've simply told you what you told us in the first place?

          • 2. Re: Newbie here, help please

            I wouldn't put the contact info fields in the "verified" table at all, only in the contacts table and would then use a Contact ID field to link contacts to the verified table. Id then use looked up value settings or import records to move data from the original table into both the second and third tables, specifying fields as appropriate.

            The real sticky issue is trying to figure out which newly imported records represent new contacts, which match to existing contacts and which represent contact info that should be used to update existing contacts. This requires knowing some context about your imported data and comparing records where the name and other contact info is similar, but may not match perfectly as the data may not be entered identically each time. (Consider the fact that if I were one of your "contacts" here, I might be recorded as "Phil" in one record and as "Phillip" in another...)

            If you can update your system so that the data is entered directly into your system via Filemaker Pro instead of importing from an Excel file, there are ways to seach for similars during data entry on a record by record basis that can make this easier to do.