12 Replies Latest reply on Jul 24, 2015 12:13 AM by electon

    Creating dynamic Value Lists

    sumaschu

      Hi All!

       

      Am trying to create dynamic Value Lists depending upon the existing data.  Have two tables that are related on an = basis.  The 2 tables are:

      1. Order.Locations (primary key Order.ID)
      2. Congregation.Locations (primary key Congregation.ID, but also has Order.ID as an indexed field which is the join to Order.Locations)

      Every Congregation within the Congregation.Locations table can have one and only one Order.ID attached to it.  At the point of data entry, the Congregation's Order may not be known, so can't make Order.ID a required field on the Congregation.Locations table.

       

      Finding Order.ID's without an associated Congregation.ID is simple.  However, when adding a Congregation to an Order, want the Value List to only show Congregations without an Order.ID

       

      Congregation.Locations table:

      Record-1:  Order.ID=1       Congregation.ID=2

      Record-2:  Order.ID=1       Congregation.ID=3

      Record-3:  Order.ID=(null)  Congregation.ID=4

      Record-4:  Order.ID=(null)  Congregation.ID=5

      Record-5:  Order.ID=2       Congregation.ID=6


      Order.Locations table:

      Record-A:  Order.ID=1

      Record-B:  Order.ID=2

      Record-C:  Order.ID=3


      So after determining Order.ID Record-C has no Congregation.Location::Congregation.ID associated with it, I want to call up that Order.ID and only show the Value List of Congregation.ID's of Record-3 and Record-4 (those Congregation.Location records without an Order.ID).


      Please let me know what questions you have.  Thanks in advance for your helpful suggestions!

      Mark <><

        • 1. Re: Creating dynamic Value Lists
          electon

          Sounds like you need a conditional value list.

          Unfortunately these don't work with empty ( null ) relationships.

           

          Finding Order.ID's without an associated Congregation.ID is simple.

           

          Put this list of Order ID's into a global field and build a  "not equal to" relationship to congregations.

          Base your value list on that TO, starting from the Main TO.

           

          Here's an example file to demonstrate. There are two techniques that may be used. If the Order ID's are number fields second one is simpler.

          • 2. Re: Creating dynamic Value Lists
            erolst

            sumaschu wrote:

            Please let me know what questions you have.

            The question is:

             

            Why do you want to have such …

            sumaschu wrote:

            only show the Value List of Congregation.ID's of Record-3 and Record-4 (those Congregation.Location records without an Order.ID).

            … a value list – and where would you use it?

             

            If your actual goal is to assign an OrderID to Congregation records that don't have one yet, then consider that this process:

            sumaschu wrote:

            However, when adding a Congregation to an Order

            happens by setting the foreign orderID in a Congregation record, not a congregationID in an Order record – which makes having such a value list pointless.

             

            What you probably want is a portal to a Congregation TO in an Order context that shows unassigned congregations, to select one or more of those and assign them the current orderID – or have a value list of all Order records (since this is a one-to-many relationship) to select & set the foreign orderID in Congregation.

             

            Is that correct?

            • 3. Re: Creating dynamic Value Lists
              sumaschu

              First and foremost, THANK you for the quick response!

               

              The question is:

               

              Why do you want to have such …

              sumaschu wrote:

              only show the Value List of Congregation.ID's of Record-3 and Record-4 (those Congregation.Location records without an Order.ID).

              … a value list – and where would you use it?

               

              Remember, the business rule is each Congregation.ID can have one and only one Order.ID.  By limiting the Congregation.ID records to only those without an Order.ID, this by definition eliminates Congregation.ID's that already have a related Order.ID and therefore can not bread the business rule of having one and only one Order.ID per Congregation.ID.  If the database allows the user to attach a single Congregation.ID to multiple Order.ID's then the rule is broken.  Hence the desire for a conditional Value List that doesn't show Congregation.ID's that already have Order.ID's.  (By the way, already have a maintenance report that brings to the user's attention any Congregations with multiple Order.ID's to help keep the database logically clean.)

               

              What you probably want is a portal to a Congregation TO in an Order context that shows unassigned congregations, to select one or more of those and assign them the current orderID – or have a value list of all Order records (since this is a one-to-many relationship) to select & set the foreign orderID in Congregation.

               

              Hadn't thought of a Layout utilizing a portal to display and elect only the appropriate Congregation.ID values.  Good idea!

               

              Thanks!

              Mark <><

              • 4. Re: Creating dynamic Value Lists
                erolst

                sumaschu wrote:

                Remember, the business rule is each Congregation.ID can have one and only one Order.ID.  By limiting the Congregation.ID records to only those without an Order.ID, this by definition eliminates Congregation.ID's that already have a related Order.ID and therefore can not bread the business rule of having one and only one Order.ID per Congregation.ID.  If the database allows the user to attach a single Congregation.ID to multiple Order.ID's then the rule is broken.

                Sorry, I don't get that.

                 

                You enforce that business rule by using a foreign key field, rather then, say, a joint table (just make sure no one misuses that field as a multi-key field). And what has one C(ongregation) record's presence or absence of an O(rder) ID to do with the presence or absence of that value in another C record?

                 

                sumaschu wrote:

                If the database allows the user to attach a single Congregation.ID to multiple Order.ID's then the rule is broken.

                 

                There is your misconception: you don't attach a C ID to an O record, because there is nowhere to put it – there is no C ID field in the O table, because you did not create it – and not creating that field (but rather the foreign O ID in C) is the implementation of your “business rule”

                 

                Order --< Congregation

                 

                This means (to repeat myself) that a value list of unattached C records is pointless; having a list (found set, related/filtered set) of these records, OTOH, is useful to assign a selected record an O ID.

                sumaschu wrote:

                By the way, already have a maintenance report that brings to the user's attention any Congregations with multiple Order.ID's to help keep the database logically clean.

                And how would it be possible for a C record to be related to multiple O records – unless there is a field you haven't mentioned?

                 

                Again, if you have the tables (and the pertinent fields)

                 

                Order (id)

                Congregation (id, id_order)**

                 

                then


                Order --< Congregation

                 

                follows not only logically, but also practically – unless somebody did put several orderIDs into Congregation::id_order (the earlier multi-line bit was meant as a joke; but maybe you should validate this field to only contain a single value)

                 

                ** You don't have to use that nomenclature, but I would strongly suggest against using periods in your field names – plays hell with, say, ExecuteSQL().

                • 5. Re: Creating dynamic Value Lists
                  sumaschu

                  Again, thank you for the prompt reply.  Appreciate your insights.

                   

                  Thanks again!

                  Mark <><

                  • 6. Re: Creating dynamic Value Lists
                    sumaschu

                    Would like to follow up on something you mentioned, just to grow my understanding of FMPa.  You mentioned:

                    Sounds like you need a conditional value list.

                    Unfortunately these don't work with empty ( null ) relationships.

                    How does one create a conditional value list if one doesn't need to worry about null relationships?

                     

                    Also, in your guidance above you mention TO.  I am interpreting this to mean Table Object.  Is that correct?

                     

                    Thanks again!

                    Mark <><

                    • 7. Re: Creating dynamic Value Lists
                      electon

                      sumaschu wrote:

                       

                      How does one create a conditional value list if one doesn't need to worry about null relationships?

                       

                      Also, in your guidance above you mention TO.  I am interpreting this to mean Table Object.  Is that correct?

                      1: You set it up however you want it to, whatever result are of interest.

                      Look in the example file I attached earlier, experiment and it will become clearer.

                      You can create a value list "starting from" another collection of data. The table occurances need to be linked in a relationship.

                      Depending on the value(s) and type of relationship you can limit the value list to specific records.

                       

                      Only if you want to filter values that contain some empty fields, you need to do it "in reverse".

                      This means get the values that are not empty and set up the ralationsip as "not these values" a.k.a. not equal to.

                       

                      It's really easier to see it in the file than explaining the possibilities.

                       

                       

                      2: TO is short for Table Occurrance in relationship graph.

                      • 8. Re: Creating dynamic Value Lists
                        sumaschu

                        electon,

                         

                        Am new to using this forum and am having trouble finding the file that is attached.  Where do I look for it?

                         

                        Again thanks!

                        Mark <><

                        • 9. Re: Creating dynamic Value Lists
                          electon

                          It's at the bottom of the second post in this thread.

                          You need to click on the thread title, I don't think it shows up when you're in the Inbox, not sure.

                           

                          I'm attaching it here as well. Just in case.

                          • 10. Re: Creating dynamic Value Lists
                            sumaschu

                            As usual, you were correct.  Attachments don't show in email view!

                             

                            Thanks for your patience!

                            Mark <><

                            • 11. Re: Creating dynamic Value Lists
                              erolst

                              This is not the correct answer.

                               

                              The correct answer is that in your scenario you don't need a conditional value list.

                              • 12. Re: Creating dynamic Value Lists
                                electon

                                If it's about the fact that attachments are easiest to access from the main thread, than it's the correct answer.

                                 

                                I've found it difficult to follow what it's all about.

                                I see "how do you create a dynamic value list of congregations not assigned to orders".

                                So trying to explain how a conditional value list works.

                                 

                                As usual the rule "don't solve the problem people are asking you to, the problem might be somewhere else...", applies.

                                 

                                And I think you're right that the way he's going about it is not the right way.