8 Replies Latest reply on Jun 20, 2015 4:57 PM by gergnnud

    Repeating field relationship in a portal.

    gergnnud

      Title

      Repeating field relationship in a portal.

      Post

      Now I know you are all going to say “don’t use repeating fields to store editable data!” but I am running into all sorts of other problems with my solution using a separate table of records that I thought it may just be that repeating fields could offer a solution.

       

      Contacts table with multiple addresses.

      Pop List for field AddressType based on editable custom value list - Address 1 street, Address 2 Postal, Address 3 Billing, Address 4 - Shipping, Address 5 Other …Edit to generate a dynamic relationship.

       

      The current solution uses a separate Contact_Addresses table and the dynamic relationship is based on a calculation field in Contacts - AddressTypeAbs = Abs(AddressType), which returns just the numeric value from the value list. A transparent portal then displays the related address fields on the Contacts layout. This is an elegant way to save space on the Contacts’ layout as only one address is displayed at any one time and unlimited addresses are possible.

       

      My problem is however that many other tables in my solution have a relationship to Contacts, e.g. Invoices, Purchases, Quotes, Personnel and on and on and on. So in order to display address details in these other tables each has to have another relationship to Contact_Adresses. The deeper it goes the more complex it gets with relationships, workaround scripts and key fields.

       

      I thought that maybe using repeating address fields in the Contacts table itself might simplify the database by drastically reducing the need for the extra relationships, key fields etc. but I cannot seem to find a way of achieving my goal.

       

      What would fit my needs perfectly would be to have say 10 repetitions of each of the address fields in the Contacts table itself. The relationship key AddressTypeAbs would remain the same but relate to the address fields’ repetition number. It doesn’t seem possible to relate to a repetition number but I might just have missed something. Just thought to post this before giving up entirely. Any thoughts?

        • 1. Re: Repeating field relationship in a portal.
          SteveMartino

          I'm not going to say it....lol, but I think the separate table is the correct way, and can be done without a lot of workarounds.  It would be helpful to see a screenshot of relationship graph.

          Keep in mind, and I'm simplifying, but if you have:

          Contact_addresses, related to Contacts, and contacts related to invoices, Contact_addresses IS related to invoices.  But that doesn't initially solve your problem.  Does the relationship have to be 'dynamic' ?  Not really.

          Or, single table.

          The real issues is, you only want to see certain addresses on certain other related tables.  So if you went with one table for contacts, and had all the fields in the contact layout, you could just put say, the billing address, on the invoices layout when you create an invoice.  You could also use a dropdown to only show/pick the one correct address.

          Let me play around with a quick sample.

          • 2. Re: Repeating field relationship in a portal.
            philmodjunk

            I get what you want to do, but relationships and repeating fields don't work that way. You end up with even more awkward workarounds trying to get to the correct repetition of the address field than you have now with related records.

            There are two basic methods for simplifying your relationship graph while still getting the correct address data:

            Use a filtered portal. If addresses links to contacts and then contacts links to invoices. A billing address can be shown on the invoices layout using a portal to addresses, but with a portal filter that specifies the "billing" address type.

            or a calculation field using Execute SQL might generate the full address in a single calculation field.

            • 3. Re: Repeating field relationship in a portal.
              gergnnud

              Thanks guys for setting me straight.

              To Steve - don't waist your valuable time on a sample. Can't really show a screenshot as my relationship graph spans about 20 pages. The question does however prompt me to do another cleanup.

              To Phil - ExecuteSQL might be a good approach to a number of issues. I believe the statement can also generate relationships on the fly. Sounds like I need to sink my teeth in anyway.

              To both - The address on an invoice must be a double entry of the contact address for compliancy. If you had the address as a calculation or a portal then this would change if the contact changed addresses. I believe my relationships are not quite right so I will concentrate on that aspect. When a solution has more than 100 tables the relationships can get daunting. I'm currently looking at naming convention to better understand how it is currently working and see if I can simplify things.

              I'll get there. Thanks again.

              • 4. Re: Repeating field relationship in a portal.
                SteveMartino

                 The address on an invoice must be a double entry of the contact address for compliancy. If you had the address as a calculation or a portal then this would change if the contact changed addresses.

                This is not necessarily true, if you set things up properly.  In the typical invoicing solution the customer name and address appear as a field (not from contact table) by calculation or lookup.  With calculation, and the box checked..."Do not replace existing data...", when you create an invoice and the address is auto entered via a calc with the do not replace box checked, changing the address in the contact table will not change the address in the existing Invoice, unless you change the invoice later in time.  It will stay there as historical data.

                • 5. Re: Repeating field relationship in a portal.
                  philmodjunk

                  @Steve, Greg is referring to my filtered portal option--which simplifies the relationship graph, but is a dynamic link to the data and thus past invoices will change when a contact address is updated. ExecuteSQL, on the other hand can be used to auto-enter the needed data.

                  Can't really show a screenshot as my relationship graph spans about 20 pages.

                  Much of the time, for a forum question, you can screen capture and crop to post just the relevant portion.

                  When a solution has more than 100 tables the relationships can get daunting.

                  I recommend taking a look at the Anchor Buoy method of organizing your table occurrence boxes into functional groups focused on specific layouts or groups of similar layouts.

                  • 6. Re: Repeating field relationship in a portal.
                    gergnnud

                    Thanks again for the responses.

                    Steve. I didn't explain myself properly. What you are saying is what I was saying, that the invoice needs to have address field/s as well. It could be concatenated as an auto enter calc, or a replication of all fields - Address_Line1, Address_Line1, City, State, Country as auto enter calcs. I do it either way depending on the circumstances.

                    I know its a bit off topic but here is a better explanation of what I'm doing and why I end up with many relationships.

                    Yes, I have anchor buoy relationship structure.

                    Attached is a snippet of the relationship graph for a Users table. There is the main relationship between table USER and table USERA for data entry of new addresses. It requires a multiple relationship to ensure the new address will be unique to the user. The Personnel_ID is an auto enter calc that takes the first letters of Name_First and Name_Middle and the last name. Users may be duplicated because they are in multiple companies. I won't bore you with why this is so. 

                    There is a button in USER-Data Entry (Table USER) to go to related records from UserA|User|ID using using layout USER-Addresses. This then displays all the addresses for that user.

                    There is a button in USER-Addresses (Table USERA) to go to related records from USER using using layout USER-Data Entry. This then returns to the USER-Data Entry layout and displays the addresses user.

                    So I can display info in USER-Addresses - Name_Full, Co_Name etc. and not replicate fields I need another relationship that looks very similar called User|UserA|ID, which is a reverse of UserA|User|ID. I can't use the main USER>USERA relationship because it will only display this info in the Address_Type selected in USER-Data Entry.

                    I'm a bit confused. It all works but am I doing this the best way?

                    • 7. Re: Repeating field relationship in a portal.
                      philmodjunk

                      If you have anchor buoy working, you might research "Selector Connector", a new version of Anchor-Buoy pioneered by Geist Interactive and SeedCode. It handles some of the issues you discuss here by setting up a special  set of "selector" and "connector" table occurrence groups that are acessible to all layouts in your file--thus reducing the need to replicate a subset of a TOG and link into different Anchor Buoy groups to implement the same set of relationships for selecting data for a particular table.

                      • 8. Re: Repeating field relationship in a portal.
                        gergnnud

                        Ahh! That is what I have been looking for. Should tidy up things nicely for me. Better get started. There's a lot of work to do.

                        Thanks Phil.