4 Replies Latest reply on Oct 14, 2010 3:19 PM by philmodjunk

    Address lookup advice required



      Address lookup advice required


      Hi there,

      I've got three tables (Organisations, Clients & Quotes) which I need to link name/address/phone details through.

      Each organisation has it's own unique file number (0001 through 9999) "kp_FileRef" and each client is linked to an organisation through a =relationship between Organisations::kp_FileRef and Clients::kf_FileRef so the plan is to be able to view related contacts using this field/relationship to sort by organisation. Each contact has it's own unique serial number so that each record is unique in some way but this number is never used in our running of the quotes/invoices.

      When I create a quote I have all the lookup fields for the name/address/email etc based on what is entered in Quotes::kf_FileRef (also =relationed to Organisations::kp_FileRef) so all three tables are linked, and if I enter 0057 in kf_FileRef on quotes, it happily looks up the related Organisation information and the FIRST client linked to that organisation but the problem comes when I need to pick a different client:

      For example, say I have four contacts for file 0057, contact one might by chance be the Chairman, so his details come up when I enter 0057 on the quote, but I want to send the quote to the Treasurer so this isn't any good.

      I was thinking of having a dropdown list of related contacts once you enter the file number and then when you pick a name it fills in the rest. I have accomplished this roughly by having Quotes::Name_Full defined as a lookup from Clients::Name_Full and then formatted the data entry as drop down list from Field: "Clients::Name_Full" and "Include only related values starting from - Quotes" This now brings up all for contact names (perfect!) but when I click it, obviously all the other lookup fields for address, etc are still on the Chairmans contact details. I think maybe it needs to somehow lookup from FileRef but then pick out the record relating to that one name?

      Also, on a side note, it would be handy to have a list of related contacts on the Client layout so we can see who else works for that organisation and what position they hold. I've seen this done in the ContactManagement solution you can download so I'm about to have a play around with that. I suspect the above might be a continuation of this sorting?

      Can anyone come up with a quick master plan to solve this using what I've already done or is this sorting business like in ContactManagement going to be the better way to go?

      Thanks in advance.

        • 1. Re: Address lookup advice required

          First, this is what you have for relationships as far as I can tell from your post:


          Organisations::kp_FileRef = Clients::kf_FileRef

          Organisations::kp_FileRef = Quotes::kf_FileRef

          You need a relationship that directly links a given quote record to one specific client record. You already have the serial number in clients (I'll call it kp_ClientID), you just need to use it in a relationship.

          Define a field in Quotes, kf_ClientID and use it in a new relationship:

          Quotes::kf_ClientID = SelectedClient::kp_ClientID

          Where client is a new table occurrence of Clients. (Select Clients in the relationship graph, click the button with two green plus signs to create this new table occurrence of clients.)

          Now place kf_ClientID on your quotes page and use a two column value list with Clients::kp_ClientID in column one and the fullname field in column 2. Use the same related values option you described for you name. Now, with this relationship in place and this added field, you can either put fields from SelectedClient directly on your layout to show the name and address informaiton or you can define matching fields in Quotes that use a looked up value field option to copy this data from SelectedClient into your quotes record. Either option works, but it depends on your business model as to which is the better option.

          Finally:  "it would be handy to have a list of related contacts on the Client layout so we can see who else works for that organisation and what position they hold."

          Just place a portal to clients (not selectedClient) on your quotes page and you can then put name and other fields from clients in this portal to get your list of related contacts.

          • 2. Re: Address lookup advice required

            Hi Phil,

            All I can say is "bravo!"

            I worked through your solution to the above - took me about 20 mins and then 10 mins checking I had everything setup right - and its perfect. Does exactly what we want. What made it even easier was that kf_ClientID was already my field name (you guessed right!) so I just worked through everything really easily! I went for the lookup option rather than a Client::field purely because we tend to deal with churches and people move around a lot between parishes, so if someone moved and we changed their address, I'm assuming it would update the old quotes/invoices if it was a Client::field which might cause problems. Lookup doesn't seem to update unless you manually reset an input value so I've gone for that one.

            In terms of the related clients, I forgot to say, it wants to be on the Client page (there will be a Related Clients tab) so will a portal to itself work?! I notice on the Contact Management solution they use a series of fields that sort, etc by different criteria. I don't need it to be anywhere near as complicated as they've made it. There's no selection process needed, just simply show the person's details, and then go to the Related Contacts tab and it shows people related by kf_FileRef. Further advice on this would be much appreciated.

            Thank you so much for this, and all previous help. The 30 day trial is soon to expire and my knowledge of Filemaker has gone from zero to......well enough to get me by! And most of it is thanks to decent people like yourself who take time to help out stupid folk like me! I think I've managed to sway the directors into purchasing with a fairly comprehensive example system so thanks again for all your help!


            • 3. Re: Address lookup advice required

              You can create yet another table occurence of clients, you could name it ClientsSameOrg and link it to clients like this:

              Clients::kf_FileRef = ClientsSameOrg::kf_FileRef

              Now a portal to clieintsSameOrg on your Clients layout will list all client records from the same organization.

              • 4. Re: Address lookup advice required

                Since you are learning FileMaker, you might find this thread helpful:  

                Tutorial: What are Table Occurrences?