14 Replies Latest reply on Oct 7, 2011 8:52 AM by mgores

    Nested Tables Relation Lookups or Script Solution Needed

    BERGSTEN

      Title

      Nested Tables Relation Lookups or Script Solution Needed

      Post

      I have three related Tables that are related by unique IDs

      Invoices-->Companies-->Contacts

      (Invoice ID, Company ID, Contact ID)

      Currently I have it set up so that a new invoice is related to a company via the company ID and related information is populated on the invoice end (Fax No, Main Phone, Billing Adress). This link also populates an invoice history in a portal on the company layout for account summaries and such.

      Each company has a list of contacts that I can look up in drop down lists from the invoice layer if I "include only related values starting from: Invoices" on the custom drop down list options.

      What my question is is this:

      Is there a way that I can pick the Contact from the drop down list on the Invoice layer and have it auto populate the cell phone, title, email, and alternate phone onto the invoice without having to type in the contact's ID. In the example database solutions for FMP11, there is an invoice option that allows you to pick a client from a list and it will populate all info without entering a customer ID, but there isn't a companies table in between like there is on my database. We need invoices related by companies because there are simply too many people we deal with at a given company through different departments so we need to know the company 1st and secondly the production manager.

      Is there a script that I can use to input a contact id even though the invoice as a record is already be related to the company id?

       

      The script for the Starter Solution is:

      "Clear Customer ID"

      If[PatternCount (Get ( ScriptParameter) ; Bill)]

      Set Field [Invoices::Bill To Customer ID; ""]

      Else If [PatternCount (Get (ScriptParameter) ; "Ship")]

      Set Field [Invoices::Ship To Customer ID; ""]

      End If

      Commit Records/Requests [Skip data entry validation; No dialog]

        • 1. Re: Nested Tables Relation Lookups or Script Solution Needed
          mgores

          You should be able pick the contact name from a filtered portal or a conditional value list filtered by companyID, when you select the contact have it enter the contactID into the Invoices::contactID field (you may need to add that field to the Invoices table and set up the relationship)  to link invoice to contact. 

          • 2. Re: Nested Tables Relation Lookups or Script Solution Needed
            philmodjunk

            First, you aren't limited to the current relationship, which appears to be:

            Invoices>-----Companies------<Contacts   ( ----< means one to many )

            You can create a new occurrence of Contacts by selecting it and clicking the duplicate button (two green plus signs).

            You can then link a ContactID field in Invoices to the ContactID field in contacts to produce this relationship:

            Invoices::ContactID = InvoiceContacts::ContactID

            Where InvoiceContacts is a new occurrence of Contacts.

            Then you can set up a conditional value list like you already describe, but specify contactID in Field1 and the Contact Name in Field 2. This way you select a contact by name but the ContactID is what is entered into the Invoices::contactID field. You would then use this new relationship to display or look up data from the contacts table.

            • 3. Re: Nested Tables Relation Lookups or Script Solution Needed
              BERGSTEN

              Ok, so I added the contactID to the invoices table and made the 2nd occurence of "Contacts" and related them via contactID. I then put a value list on Invoices::ContactID specifying Contacts2::Contact_ID and "Contacts 2:: Full_Name" Including only related values starting from Invoices and showing only values from the second field. (I don't want anyone getting confused by id #s). 

              Then I allowed entry of other values.

              Now it will not populate contacts from the company. I either get every company when I check "Include all values" or nothing when I check "Include only related values starting from: Invoices"

               

              Also, when I pick a name from the list, it doesn't populate any data into other fields do I just use standard lookups for these now?

              EDIT

              I actually got the list to work from the 1st occurence of the contacts table. Can I still do it this way. I hit the arrow, it pulls up everyone at that company and I pick the number and this will lookup all the relevant fields.

              Is there a way that I don't have to have the id field visible though? Where I can pick the name and have the id be entered into a field not on the layout?

              • 4. Re: Nested Tables Relation Lookups or Script Solution Needed
                mgores

                You don't want to use the Contacts 2 table for the value list as it will only show contacts related to that invoice, which would be none.  For the value list use the relationship where contacts are related to company.

                You should be able to set up the lookups for phone, fax, email, etc to work from the contacts 2 to invoice table then.  Or you could just make those fields   Contacts 2::phone, etc

                • 5. Re: Nested Tables Relation Lookups or Script Solution Needed
                  BERGSTEN

                  Cool. That makes sense. 

                   

                  Also, if I want to associate multiple contacts to the invoice how do I go about that, do I still need a linking table then?

                   

                  Ex. The first person is the production manager, but another is the billing contact.

                  • 6. Re: Nested Tables Relation Lookups or Script Solution Needed
                    mgores

                    I think that would take another ContactID field in the iinvoice table (maybe PMcontactID and BillingcontactID).  It would require another TO of contacts for the second relationship as well.  I would rename those as BillingContact  and ProductionContact (or whatever makes sense to you) rather than having Contacts 2 and Contacts 3.

                    • 7. Re: Nested Tables Relation Lookups or Script Solution Needed
                      BERGSTEN

                      Ok. Nice. 

                      Is there a way to make this more fluid. Ideally I'd like to pick someone from the list, have their name stay in that box and populate the other fields almost simultaneously, like in the starter solution for Invoices that comes in FMP9

                      • 8. Re: Nested Tables Relation Lookups or Script Solution Needed
                        mgores

                        Say you have set up the production manager to be based on ProductionContacts table.  If you set the fields for his phone, fax, email on the invoice layout to be ProductionContact::phone,  ProductionContact::fax, ProductionContact::email   they should populate as soon as the Invoices::PMcontactID is selected from your drop down.

                        • 9. Re: Nested Tables Relation Lookups or Script Solution Needed
                          BERGSTEN

                          Can those still be editable from the invoice side though without compromising the data in the contacts table?

                          • 10. Re: Nested Tables Relation Lookups or Script Solution Needed
                            philmodjunk

                            They would be editable unless you prohibit field entry. The edits would modify the original contact record. If you don't want that possibility, you can define matching fields in Invoices and use the looked up value field option to copy the data from the contact table via the relationships Mark has had you set up here. Since the data is now copied, you can edit them without modifying the original contact record in the contacts table.

                            • 11. Re: Nested Tables Relation Lookups or Script Solution Needed
                              BERGSTEN

                              Ok thanks! This should work!

                              • 12. Re: Nested Tables Relation Lookups or Script Solution Needed
                                BERGSTEN

                                For my billing contact, I have a calculation of the following or similar on the other fields I want to copy over:

                                If ( not  IsEmpty (Same_As_Billing_Contact) ; Prod_Mgr_Cell ; Billing_Cell )

                                If the check box is checked, then it copies the production managers information into the billing fields.

                                Is there a way that I can have both the method for the lookup described above and this checkbox calculation to make it easier when the billing contact isn't the same as the production manager (which is about 50% of the time)?

                                • 13. Re: Nested Tables Relation Lookups or Script Solution Needed
                                  philmodjunk

                                  Two options you can play with to see which works best for you:

                                  1) You can use looked up value fields to copy over the three fields referenced in this calculation. Then define this same calculation field in your invoices table. With this option, you can click the check box on the invoices layout to make a change specific to that one invoice.

                                  2) you can use a looked up value field to copy the value of this calculation field into a text field. In this case, you only need one field instead of 4, but now cannot change the value of that check box and have the change only affect the current Invoice.

                                  • 14. Re: Nested Tables Relation Lookups or Script Solution Needed
                                    mgores

                                    You could have that check box calculation run as a script (triggered from whatever event would work for you) replace the looked up value if the box is checked.  If the box is not checked it will leave the looked up value.