13 Replies Latest reply on Aug 21, 2014 10:45 AM by tays01s

    Conditional value lists

    tays01s

      Title

      Conditional value lists

      Post

      A.

           I have 2 tables: DrugsUsed > Drugs. However; I'd like to have a 3rd table on which to store 'Configurations'. The latter would list a drug in terms of: Company; Country; Product. I'd like to make these checkbox fields to specify a custom 'DrugList' with Country conditional to Company and Product conditional to Country.


            

           I am familiar with conditional value lists but have 2 Qs:

           1. Product is a long list so really needs a scroll bar. How do I get that when using a conditional list on a checkbox field?

           2. I have related 'Config' table > Drugs [Config::_DrugsID > __DrugsID] and Config > Drugs2 (TO of Drugs) [Config::Company & Country & Product to Drugs2:: Company & Country & Product; respectively]. However; checkboxing in Config::Company has not effect on the conditional value lists for Country & Product.

      B.

           I have a Patient table & layout. Patient::Name field is a pop-up. I'd like this to populate depending on whether I specify 'In-use' or 'Filed' from the Patient::Status field. I created a Patient2 TO and related Patient::__ID to Patient2::__ID and Patient::Status to Patient2:: Status.

        • 1. Re: Conditional value lists
          philmodjunk

               1) You can't, but you can replace the value list format with a portal where there are controls that look and function like check boxes but which are really buttons that perform scripts with conditional formatting or "hide object when" properties that change the object's appearance to simulate a check box. Since this is a portal, it can have a scroll bar.

               2)  Your first relationship is:

                    Config::DrugsID = Drugs::__DrugsID

               But is this the second relationship?
                    Config::Company = Drugs2::Company AND
                    Config::Country = Drugs2::Country 'AND
                    Config::Product = Drugs2::Product

               Isn't Product a field that uniquely identifies a single product? (If so, it would make the first two pairs of match fields superfluous.)

               What do you mean exactly by "check boxing in Config::Company?

               How exactly is your conditional value list defined. From what table occurrence does it draw values and on what table occurrence is your layout based?

               

                    I'd like this to populate depending on whether I specify 'In-use' or 'Filed'

               Does selecting "in-use" add the patient to the list or does selecting "Filed" add the patient to the list? We can set up this list with a calculation field defined in patients that does not need any relationship or added table occurrences in order to make it work.

          • 2. Re: Conditional value lists
            tays01s

                 1. OK

                 2. "Your first relationship is:

                      Config::DrugsID = Drugs::__DrugsID"    YES

                 - But is this the second relationship?
                      Config::Company = Drugs2::Company AND
                      Config::Country = Drugs2::Country 'AND
                      Config::Product = Drugs2::Product

                 Isn't Product a field that uniquely identifies a single product? (If so, it would make the first two pairs of match fields superfluous.

                 [TRUE. Do I need a Drugs TO to produce each level of the conditional list?]

                 - What do you mean exactly by "check boxing in Config::Company? [Sorry, I just mean that if I activate a checkbox for eg. Company_A, I'd like all the Countries that it has products in to be listed (and only those, unless there is another company with different countries of course).

            • 3. Re: Conditional value lists
              philmodjunk

                   So Config::Company is formatted with a group of check boxes? And you need your value list to list values from Drugs?

                   Then the typical conditional value list would use this relationship:

                   Config::Company = Drugs::Company

                   Your value list would list values from Drugs and you'd select the "include only related values, starting from Config" option at the bottom of the "use values from a field" dialog inside Manage | Value Lists.

              • 4. Re: Conditional value lists
                tays01s

                     I currently have relationships:

                     1. Drugs::__ID to Config::_DrugID &

                     2. Config::Company to Drugs2::Company [Drugs2 being a TO of Drugs)

                     The Config value list needs the full list of companies so is based on Drugs2::Company, include all values. Where I get stuck is getting Companies to appear only if a Company markets in that Country and Products to appear only if they are available in that Country. The Country and Product value lists come up with 'no values'.

                • 5. Re: Conditional value lists
                  philmodjunk

                       Config::Company to Drugs2::Company

                       With that relationship, Your value list's "show values from" setting should list values from Drugs2 and specify Config as the starting from table occurrence.

                       This assumes that your layout is based on Config.

                  • 6. Re: Conditional value lists
                    tays01s

                         Yes, I've done that, though because I want all Companies to show, as it's the primary list, I've set Drugs2::__ID & 2nd field Drugs2::Company to 'show all values.

                         The problem is when I also set relationships:

                         Config::Country to Drugs2::Country &

                         Config::Product to Drugs2::Product

                         Using Drugs2::__ID & Drugs2::Country or Product with 'show only values from Config (yes that is the layout), I get 'no values' listed, let alone a value list conditional in the order: Company > Country > Product.

                    • 7. Re: Conditional value lists
                      philmodjunk

                           We are talking about different value lists. The value list for one field, config::Company, should be for selecting companies--for which you've described a check box format. That value list should NOT be a conditional value list and should list all companies.

                           But this is not the value list that I have been describing. I'm describing a conditional value list for Config::Drugs where, after clicking a check box for one or more companies in the Config::Company field, lists only the drugs for those selected companies. It's been my understanding from the beginning of this thread that this was what you wanted to see happen here.

                      • 8. Re: Conditional value lists
                        tays01s

                             Yes, the Company value list is not conditional.

                             The value lists for Country and Product are Drugs2::Country / Product. Company, Country and Product value lists are displayed from data on the Config layout using the Config::_DrugsID fk field.

                             With this arrangement, the Company list is complete, Country only displays a duplicate of a single country no matter what Companies are selected and if I check/uncheck the duplicate 'Countries' that appear, only 1 alters the Product list.

                        • 9. Re: Conditional value lists
                          philmodjunk

                               I'm sorry but:

                               

                                    The value lists for Country and Product are Drugs2::Country / Product. Company, Country and Product

                               confuses me. I can't tell what precisely you are describing there. You have two fields with what should be formatted with two different value lists. I can't tell which is what in that sentence.

                               Your Config::Company field should use a value list of all companies. your Config::_DrugsID fk field should be formatted with a list of all drugs specified for that company or (if you click multiple company check boxes) companies.

                          • 10. Re: Conditional value lists
                            tays01s

                                 Apologies. My Config::Company does have a value list for all companies. I then want

                                 - Countries to be conditional to Company and have used the Config::_DrugsID field with the value list: Drugs2:ID and 2nd field Drugs2::Country listing only related values from Config

                                 &

                                 - Products conditional to Country again using the Config::_DrugsID field with the value list: Drugs2:ID and 2nd field Drugs2::Product listing only related values from Config.

                            • 11. Re: Conditional value lists
                              tays01s

                                   Since the last post, possibly some progress. I've now got a checkbox Config::Company field that with conditional listing:

                                   1. Config::Country, list: Drugs::Country related values starting from Config and using the relationship Config::Company to Drugs::Company.

                                   2. Portal from Drugs2 with fields from Drugs2::Drug & Drugs2::DrugChoice (checkbox field) using the relationships Config::Company to Drugs2::Company + Config::Country to Drugs2::Country.

                                   When I choose a Company the correct Countries are listed and choosing a Country the Correct Drugs are listed.

                                   Qs:

                                   a) Because the number of Countries might grow long, I tried to create a portal for 1. using the Drugs table & Drugs::Country field, but it lists ALL records.

                                   b) The Drug2::Choice allows me to specify those drugs I wish to appear in a user choice on another layout. However, for an ongoing patient, previously chosen drugs are recovered when the patient record is chosen. How will I permit these drugs to be shown, but limit future drug choice to whatever choice has been specified in the current Config?

                              • 12. Re: Conditional value lists
                                philmodjunk

                                     a) you would need a table of just Country records to eliminate the duplication of values. This sounds like a good design option for your database.

                                     b) please keep in mind that I have no real idea what you are trying to manage with this database. Each post you make reveals a bit more but I am still largely in the dark as to what you are trying to do with these tables from a "big picture" perspective. That means that my advice might be correct for the problem as specifically described but not work in terms of meeting the overall goals for your database solution.

                                     

                                          However, for an ongoing patient,

                                     Does that mean that you have a Patients table? How is it related to these other table (occurrence)s?

                                     

                                          How will I permit these drugs to be shown, but limit future drug choice to whatever choice has been specified in the current Config?

                                     Sorry, but Huh? There's just so much that I don't know about what you are trying to do here that I don't know what process you are actually describing here.

                                • 13. Re: Conditional value lists
                                  tays01s

                                       a) When you say I need a table of just Country records do you mean another drugs TO along with relationships, or literally a table separate from Config and Drugs but related to them?

                                        

                                       b) Before dealing with the more complex side of this Q can I ask: Once I've got Company, Country, Drug checked as desired on Config, there will be a checkboxed field listing 'active' Drugs on the Drugs table. How do I get the list to appear on a related layout?