5 Replies Latest reply on Jan 28, 2015 7:26 AM by dsimonson

    Getfield() question

    dsimonson

      Title

      Getfield() question

      Post

      I am writing a script where I want to gather an amount, put it in a variable, and then go to a different layout and add that amount to a field whose name I will set using another variable.  Not having any luck.  

      The field I want to add to is called med_amt_1, med_amt_2, and so on.  I was hoping to be able to identify the field by just concatenating a number to the end of "med_amt_", as in "med_amt_"&MedTime (MedTime is a number, 1 - 40)

      Here's how I set the variable, $FieldContents: 

      Let (
      @fieldname =  "surgeries_GRAPH_MEDS_15_MIN::med_amt_"&$MedTime; 
      GetField("\"&@fieldname&\""))

      at present, this returns "?".  Note that I tried to deal with the quotes by escaping them, I don't know if that is helping or hurting.   

      Once I get this variable to add the new amount to the current contents of the med_amt, I want to replace the contents of the field.  

      Thorny problem!  I hope the answer isn't too easy... ;>)

        • 1. Re: Getfield() question
          philmodjunk

          Your use of names where the only difference is a number at the end strongly suggests that you have a less than optimum data model. Such a group of similar fields are almost always something better managed as a set of related records instead.

          GetField reads data via indirect data reference. Set Field By Name writes date to an indirectly referenced field.

          Both require text that names the field using the folloiwng format: "TableOccurrenceName::FieldName".

          In your example, you should be using GetField (@fieldname )

          no quotes are needed.

          • 2. Re: Getfield() question
            dsimonson

            I was worried you would catch me in this bad habit.  You are correct in that conceptually, I should be able to do this with related records, but I just haven’t been able to figure it out.  

            Here’s the issue.  My anesthesia record solution lets the user record “observations” that are timed.  For example, the user can give a drug, midazolam, 2 mg, at 10:17.  She can then give more midazolam, another 2 mg, at 10:22.  Each of these “observation_meds” records has the following fields (and others, but these are the important ones):

            fk_med_id

            fk_surgery_id

            Med_amt

            Time_med (the exact time the user gave the med)

            Time_med_15_minutes (time_med rounded down to the nearest 15 minutes).

            Now that I have a bunch of these observation_meds records, I want to place them on the “anesthesia record”, which is actually a report that summarizes the records into 15 minute bunches.  In our example, that would mean I would add both of the records entered above and produce one summary field for that 15 minute period, to be displayed as midazolam 4 mg in the 15 minute period starting with 10:15.  

            Here’s what it is supposed to look like in both Browse and Layout view2015-01-27_06-53-51.png2015-01-27_06-53-31.png

            (I am uploading a screen shot in case this doesn’t come through)

            The problem (and you will note I have brought this up before), is that portals will work - but I can’t figure out how to get them to display the data horizontally.  They are set up (as of course you know) to show rows of data vertically.  My solution, which I hope you will criticize roundly ;>) was to create a script that would add the amounts together and stuff them into a separate table (GRAPH_MEDS_15_MIN) which has the following fields:

             

            _fk_med_id 

            _fk_surgery_id 

            graph_data_med_page 

            med_amt_1 

            med_amt_2 

            med_amt_3 

            med_amt_total 

            Once I have done that, it is fairly straightforward to create a portal to these records, and just put one med summary record per line, with the various time blocks (which I have calculated when the user enters the anesthesia start time) arrayed horizontally.

            If I were to do this with “mini-portals” consisting of one field from one record, I would need a new portal for each record (med) and time block - in my example, a portal looking from surgeries -> observations_meds, and displaying a summary field (after sorting by med and time) for each 15 minutes.  On line 1, the first portal would show the first drug name.  The second portal just to the right of it would show the sum of all of the entries for that drug in the first 15 minutes.  The second portal, placed just to the right of the first, would show all of the entries for that drug for the 2nd 15 minute period, and so on, with the farthest to the right showing a total.

            And then, repeat this on the second line down with the 2nd med - and the number of meds would of course be variable depending upon the case.  Note that I have a vertical scroll bar on the portal to allow for this.  Ideally all of the meds would show up, so when printed out this would be a complete record, but I think I will have to have a separate med sheet print out if they exceed the limit I can reasonably display on this record.

            As I am describing this to you (and this is one of the benefits you provide - making me write this stuff down and thus think it through), it appears to me that this might be made to work - but is it the most elegant solution?  I really feel like I’m stumbling around here.

            Thanks so much, Phil - you are a great teacher.  Bring on good database discipline!  :>)  If I ever get this done, I will certainly credit all of your help.

            Dan

            • 3. Re: Getfield() question
              philmodjunk

              but I can’t figure out how to get them to display the data horizontally.

              The horizontal portal technique is well known and often used.

              Each "field" in your row can be a one row portal. This row of portals can be set up with "initial row 1", "Initial row 2"... or a different portal filter expression can refer to different records--such as specifying a time period. Using a portal filter and a summary field, you can even refer to groups of records for each spot in the row with a summary field to produce a sub total or average based on that group of records.

              • 4. Re: Getfield() question
                dsimonson

                Ok, I'm on it.  I'm determined to make this work!  Having to write it down and listening to your points gave me more ideas.  

                • 5. Re: Getfield() question
                  dsimonson

                   

                  Ok, just to follow up - I finally (I think!) figured it out.  The difficulty I was having was understanding portals work in the situations I was encountering.  I kept getting hung up because I was using the wrong context.  It took me a lot of trial and error to get it right, but now it is working.

                  Thanks, Phil.

                  Dan