8 Replies Latest reply on Aug 15, 2011 10:43 AM by BarryIrvine

    Related tables and portal controls

    BarryIrvine

      Title

      Related tables and portal controls

      Post

      Hi,

      I'm new to Filemaker but very experienced with relational databases but I'm struggling getting to grips with the layout controls regarding one-to-many relationships.

      I have a Customer record with a cust_id serial number primary key and I have linked this to a table Address with a relationship based on the cust_id. it should be possible to have multiple addresses in the table for a customer. E.g. Work or Home (I've defined this as a radio button set on a field called Address Type). There is a unique key constraint on this table based on the calculated value of "Address Type & cust_id - e.g. it is only possible to have one address of each type per customer. I have added the address table to my customer layout and by defining the "Allow creation of records in this table via this relationship" and "Delete related records..." I should be able to add and create Home and/or Work addresses for Customers from the main layout.

      In broad terms this works but obviously when I change the value of the radio button it doesn't automatically query the data in the address table if it already exists for that Address Type.

      I then tried adding a portal to Address to my layout instead but I can't make head nor tail of how I'm supposed to get this working for a relationship where 0, 1 or 2 records could exist for the Customer.

      Can anyone give me some tips as to where exactly I'm going wrong?


      Thanks in advance,

      Barry

       

        • 1. Re: Related tables and portal controls
          mgores

          First make sure the layoot you have the portal on is based on your Contact table.  Then if you have Contacts related to Addresses by cust_id select that relationship for the portal, size it and place the fields you want from the Address table in it.  Select how many portal rows (addresse) you want to see or select show scroll bar.  In browse mode for each Contact record you should see all of the addresses with the same cust_id.

          On the calculated field, it should be a field type of calculation with the storage option set to recalculate, not as an autoentered calculation.

          • 2. Re: Related tables and portal controls
            BarryIrvine

            Hi Mark,

             

            Thanks for the information. I'll give that a go although at the moment I've created a tab control with two portals in it - one for Home address and one for Work address. Which isn't really how I wanted to do it - it becomes a bit inflexible if I add new address types and will look positively bulky when I do something similar for phone types (Home, Work, Mobile).

            Wrt to the calculated field if I create it as field type calculation I don't seem to have an option to set a validation on it for "Unique value" which is the only reason I created the field in the first place. How can I set this sort of validation on a calculation field?

            Regards,

            Barry

            • 3. Re: Related tables and portal controls
              philmodjunk

              There is a unique key constraint on this table based on the calculated value of "Address Type & cust_id - e.g. it is only possible to have one address of each type per customer.

              Actually, this should be a text field with an auto-entered calculation and with the "Do not replace existing values..." option enabled so that changing an address type updates the value of this field. This enables you te define a unique values validation rule on this field. (If you use a field of type calculation, the validation rule will not be applied to the value of the field.)

              If that doesn't fully solve your issues here, please post back with a description of exactly where you have the issue.

              • 4. Re: Related tables and portal controls
                mgores

                Yes, Phil is right.  I mix up those two options sometimes Embarassed

                In similar situations I just have a text field for "type" where I enter bill to, ship to or home, mobile, fax, etc.  You can then have a "type" field in your main table where you can select the type and have a portal filter based on that field value.

                • 5. Re: Related tables and portal controls
                  philmodjunk

                  It's also possible to set up your value list for type as a "dwindling value list". This is a conditional value list that updates for each customer record so that selecting a type removes it from the list so that it cannot be selected a second time for that customer until you either delete the related record of that type or change it's type. This interface design--which does not require scripting in this scenario--avoids tripping the validation error that you've selected the same address type twice for the same customer in the first place.

                  • 6. Re: Related tables and portal controls
                    BarryIrvine

                    Thanks all. This is really useful stuff. I'm still struggling though with the portals. If I create an email_type field on Customer and then link that to a portal for email with a filter on email_type it works fine when there is no entry for that customer. However, if I've already entered a Home email address and then I switch the radio button to Work - it still has the data for the Home email address inside the portal. It doesn't seem to be refiltering the results when the button is changed.

                    • 7. Re: Related tables and portal controls
                      philmodjunk

                      This is a known limitation to FileMaker 11. Here's the workaround:

                      Add a script trigger that uses ONObjectModify on the radio button field to perform this one line script:

                      Refresh Window [Flush Cached Join Results]

                      This will force the portal to apply the new value to re-filter the portal.

                      • 8. Re: Related tables and portal controls
                        BarryIrvine

                        Fantastic. That works a treat. Thanks Phil. I tried to give your answer the best answer badge but somehow clicked my own post! I can't seem to remove the best answer tag from my own post to reapply it to yours! :(