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.
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):
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 view
(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:
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.
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.
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.
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.