1 2 Previous Next 18 Replies Latest reply on Oct 31, 2014 12:52 AM by tays01s

    'Looking up' a value

    tays01s

      Title

      'Looking up' a value

      Post

      I want to lookup a value based on a concatenated field: Age&Sex&Std_name. Age is a problem because it's continuous whereas each Std_name has specific age categories. So, I need to first use Age to find the nearest matching (<) Age_category. I'd then use Age_category in place of Age in the concatenated field in order to find the value in that matching record.

      How would I go about finding the nearest matching (<) Age_category?

        • 1. Re: 'Looking up' a value
          philmodjunk

          Don't use a concatenated field. Use a relationship where the three original age, sex and Std_name fields are all match fields in the defined relationship. You can then use inequality operators with the age field as part of your relationship.

          Under some circumstances, you might script a find using the values in these three fields to find the record(s) matching the specified data.

          Please note that the look up method that you are using could match to more than on individual so there are other ways to search out a record using this data that you may want to consider that would have a designed in ability to detect and handle matches to more than one record.

          • 2. Re: 'Looking up' a value
            tays01s

            OK. Might quite complex though. 3 tables, so I've related:

            Patient (TO)::Sex = Accret::Sex

            Config (TO)::Std = Accret::Std

            I'm not sure how to get he highest Accret::Age value that is <Calc::Age?

            I'm also not sure how the Calc::Accret will lookup the Accret::Accret value from the above relationships [not having used a lookup before].

            • 3. Re: 'Looking up' a value
              philmodjunk

              Your original post did not indicate that there were more than two tables involved so my suggestion was only made from that incorrect assumption (Darn crystal ball was on the fritz again wink). Please describe all your tables and relationships involved and we'll take another try at this.

              • 4. Re: 'Looking up' a value
                tays01s

                Please relationship graph attached. Patient 2 can be ignored for this problem.

                Patient::__ID = Calc::PatientID

                Patient 3::Sex = Accret::Sex

                Config (TO)::Std = Accret::Std

                Calc::Age ?? Accret::Age [I'm not sure of how to select this relation. I need Calc::Age to be < Accret::Age but otherwise the next highest value of Accret::Age)

                I want the lookup value to appear in: Calc::Accret

                Thanks

                • 5. Re: 'Looking up' a value
                  philmodjunk

                  I see no attached relationship graph. Please make sure that any uploaded file is of the correct file format. If you upload a PDF, for example, the forum software will upload your file, but fail to display it.

                  • 6. Re: 'Looking up' a value
                    tays01s

                    Sorry, here it is.

                    Please relationship graph attached. Patient 2 can be ignored for this problem.

                    Patient::__ID = Calc::PatientID

                    Patient 3::Sex = Accret::Sex

                    Config (TO)::Std = Accret::Std

                    Calc::Age ?? Accret::Age [I'm not sure of how to select this relation. I need Calc::Age to be < Accret::Age but otherwise the next highest value of Accret::Age)

                    I want the lookup value to appear in: Calc::Accret

                    • 7. Re: 'Looking up' a value
                      philmodjunk

                      I don't see how that can work--even if working from "paper" records.

                      Calc, has the needed ID and Age values, but there is no link between Calc and config 2 so I don't see how std can be part of the criteria used to look up a value from Accret. If you can get a field in Calc with that value, we can do this.

                      • 8. Re: 'Looking up' a value
                        tays01s

                        I think before I move forward with the above question, I first need to ask something about 'Config'. Config is a table/ layout that determines various standards (eg. Field: Std), conditional value lists, thresholds etc.. The conditional value lists will be visible on the patient layout. Would I therefore be best to relate Config as a child of the Patient table so that a patient might have a >1 mix of configurations?

                        I would then need to look at how this relationship affects Calc and Accret.

                        • 9. Re: 'Looking up' a value
                          philmodjunk

                          Sorry but there's not enough detail in your description of what you are trying to set up for me to have a suggestion for you.

                          • 10. Re: 'Looking up' a value
                            tays01s

                            Not the complete solution but as an example here are some tables:

                            Parent       Child       Grandchild       Greatgrandchild

                            Patient    < Calc     <  Feedused       < Feeds

                                                              Equations (of Calc)

                            So for example, I'd like to have conditional value lists for Equations and Feeds on the Patient layout. These value lists would be determined from another table/ layout called Config. Normally you would use only 1 Config setting/record for 1 patient. However, it's possible over time that Feeds or Equations will be updated and a new Config setting/ record would be required.

                            Config will affect so much else I want to be sure I get its position right within the relationships. I assume I should make it a child of Patient? 1 other point. Within Config I would want 2 default records: a. Factory and b. User. a. would be set when the solution is issued and b. could be updated by the user.

                            • 11. Re: 'Looking up' a value
                              philmodjunk

                              But how does the value in std affect what value is looked up in the relationship? Is the value in std a value specified for the entire database? Once patient? A group of patients?

                              • 12. Re: 'Looking up' a value
                                tays01s

                                The Std is one of 3 global regions, eg. AusNZ. This value would generally be chosen in that region and apply for a patient, but I want to leave open the possibility that for a single patient, the user may wish to use a different Std for a different Calc.

                                • 13. Re: 'Looking up' a value
                                  philmodjunk

                                  That still leaves things unclear.

                                  is the value of std a value that can be different for every record in your patient table?

                                  If so, why is it not a field in that table?

                                  A config table sounds like a table where you "configure" the system for a specific installation, a specific user, or a specific group of users. That then makes sense to have a config table. But if you are going to specify specific parameters for every patient in your patients table, then it doesn't make sense to me to put this value in a different table.

                                  So if you have multiple patients and some are from different global regions, you need to specify that as a part of the patients record so that this value can change when you select different records from that table as the basis for looking up this data.

                                  • 14. Re: 'Looking up' a value
                                    tays01s

                                    The Std could be different for different patients. However, if I were in Australia, once I'd chosen the AusNZ Std, I would usually leave it as that for all patient. However, for example the UK Std is out of date, so a UK user might occasionally use a more modern Std like AusNZ.

                                    For now I've decided to put Std on Calc. So relationships are [see attached]:

                                    Accret::Age ? Calc::Age   [? denotes that I'm not sure how to relate these. The Accret::Age needs to be the highest value = to or < Calc::Age.

                                    Accret::Std = Calc::Std

                                    Accret::Sex = Patient 3::Sex

                                    1 2 Previous Next