6 Replies Latest reply on Mar 28, 2013 12:45 PM by philmodjunk

    related tables, table occurences, layout, and value list question

    JasonO'Berry

      Title

      related tables, table occurences, layout, and value list question

      Post

           Hi everyone, 

           I'm having a hard time trying to understand the relationships between table occurences, portals, layouts, and conditional value lists. I understand them all individually but when combined they are throwing me for a loop. 

           From reading posts here and elsewhere, it seems that a value list attached to a field in a portal need be shown on a layout that is related to the portal table occurence. I was doing some cleanup in a solution and made some changes to clean up 4 or 5 different relationships i had stemming from my invoice table. I created a new table occurence of invoice details  (did not create a relationship between original invoice table and new invoice details TO) and readded the relationships to this new occurence.

           I had remembered something about it needing to be related to the layout I am working on so I started researching. The strange thing is is that it seems to work just the same as it was before I made the changes. But i dont want it to cause problems in the future so I rather get it right the first time. 

           Does the layout that I am working on ( invoice with a portal to invoice details) have to have a relationship to the second Invoice Details table occurence for the conditional value list to work correctly.

           A clear explanation would be very much appreciated as I dont want a fix, i just want to understand. Thank you much.

      Untitled.jpg

        • 1. Re: related tables, table occurences, layout, and value list question
          philmodjunk

               Whether or not your value list continues to work correctly after such a change will depend on the specifics of the value list's setup. If they have the "use values from a field" option, but do not use the "include only related values.." option, then no relationships are involved in the function of the value list and the change you have made will have no effect. Hmmm, one little exception comes to mind. Your value list can show data from a primary and secondary field. If the primary and secondary field data come from two different tables, the relationship between the two tables must be preserved. (This is a pretty rare way to setup a value list.)

               However, if you have used the "include only related values..." option to make this a conditional value list, then a change that alters your relationship could keep the conditional value list from working. It depends on the "starting from" table occurrence you specify in this option.

          • 2. Re: related tables, table occurences, layout, and value list question
            JasonO'Berry

                 Hi PhilModJunk, 

                 yes I am using "include only related fields in the value list setup". So do i just need to add a relationship from my original INVOICE table occurence to the new INVOICE DETAILS table occurence: INVOICE::pk_invoice_id-----------<Available Sequence Numbers__INVOICE DETAILS__fk_invoiceID and all would be right in the world.

            • 3. Re: related tables, table occurences, layout, and value list question
              JasonO'Berry

                   Actually, in testing I created the realationship from the original INVOICE table to the new occurence group by INVOICE::pk_invoiceID ------< Available Sequence Numbers__INVOICE_DETAILS::fk:invoiceID and set the value list to show related records from the INVOICE table, and it broke the value list.

                   If i add 1 item in the portal then it worked fine but if i added a second line item then any subsequent line items i added would show all related sequence number as available to all prints instead of individually. (confusing if youre not privy to the database operation). I guess because the value list was showing related records from the invoice relationship which is not specific enough to get the desired values. Thats why context, table occurences, portals, and relationships used in conjunction with each other confound me.

              • 4. Re: related tables, table occurences, layout, and value list question
                philmodjunk

                     This is an interesting case of determing "Table Occurrence Context". Is it determined by the layout or by the details specified in Manage | Database?

                     Note that you have the "Starting from" table occurrence, Available Sequence Numbers__INVOICE_DETAILS linked to the value source table occurrence Unused Squence Number__SEQUENCE_NUMBERS.

                     I haven't tried this particular scenario, but if it works, no need to make any changes.

                     If it doesn't then you'll need to base your value list on an Occurrence of Sequence numbers that is linked to the other occurrence of INVOICE_DETAILS and specify the other occurrence of Invoice Details as the "starting from" occurrence. But only if the valuelist doesn't work.

                • 5. Re: related tables, table occurences, layout, and value list question
                  JasonO'Berry

                       One last thought, 

                       if I set the value list to use related values from the 2nd INVOICE DETAILS table occurence NOT from the INVOICE table it works fine whether I relate it to the original INVOICE table.

                       But if i add the relationship from original INVOICE table to the new INVOICE DETAILS and set the value list to use related values from INVOICE then it breaks. 

                       Why does it need a relationship from the INVOICE table (the table my layout is based on) if its set to use related values starting from a different table occurence.

                       Sorry, i think I'm confusing myself more now.

                  • 6. Re: related tables, table occurences, layout, and value list question
                    philmodjunk

                         And what relationship did you add?

                         The settings inside Manage | Value list for a conditional value list specify a relationship by identifying two table occurrences for FileMaker to use in order to pull up a list of values. The first table occurrence is specified in the drop down for the primary field. The second table occurrence is the "starting from" occurrence specified at the bottom of the dialog. It's the relationship between these two table occurrences that control what values are produced for the value list.