| Calculated field is updated only for selected record in tabular form [message #140161] |
Sat, 01 October 2005 11:02  |
adamjsawyer
Messages: 79
Registered: April 2005
Location: Perth, Western Australia ...
|
Member |
|
|
Hi I have a form with a Master (form), Detail (tabular) and Sub-detail (tabular) blocks on it, created with Design Editor (Trials, Samples, Staples). The Samples block need two calculated fields. The first one, Yield, was easy and I implemented it using the calculation portion of the property palette.
The second one was a problem. It needs to perform an average of a column in the Staples table. My first attempt was to make a summary calculated field (using property palette) and read it into the text item in the Sample block. The problem here was that all of the calculated fields were getting the same result, based on only the Staple data currently on the form. This was no good, and I realised I was going to have to write some code that performed SQL.
Well I did that, and not worrying about triggers for now, I just made a button to perform the update. It works, however it only updates the Sample that is currently selected on the Sample detail, not all of them. I need to be able to update the calculated field for all samples in the tabular display.
Here is the code for the update button:
PROCEDURE UPDATE_LENGTH IS
avg_length NUMBER(4,1);
BEGIN
SELECT AVG(length)
INTO avg_length
FROM wtl_staples
WHERE smp_sample_id = :WTL_SAMPLES.SAMPLE_ID;
:WTL_SAMPLES.STAPLE_LENGTH := avg_length;
END;
I hope someone can help
thx
adam
|
|
|
|
| Re: Calculated field is updated only for selected record in tabular form [message #140352 is a reply to message #140161] |
Mon, 03 October 2005 19:42  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
How about putting this code in the post-query of your middle block. Then it will have the correct ':WTL_SAMPLES.SAMPLE_ID' for each of the calculations.
Alteratively, you will need to 'loop' through your middle block. Use 'first_record', 'last_record', and the block attributes 'current_record' and 'top_record' to get the cursor to go back to the correct line. All of these terms exist in the documentation and have been mentioned in this forum.
David
[Updated on: Tue, 04 October 2005 17:49] Report message to a moderator |
|
|
|
| Re: Calculated field is updated only for selected record in tabular form [message #140418 is a reply to message #140161] |
Tue, 04 October 2005 03:10 |
adamjsawyer
Messages: 79
Registered: April 2005
Location: Perth, Western Australia ...
|
Member |
|
|
I have got the fields calculating now, by looping through the Sample section and updating for each one. Initially, I was reading the Staples block for the staple data, but then every sample was getting the same results. Stupid me... so I ended up using an SQL query to read from the database, and hope the user hasn't got unsaved data in the Staples block. Well, I guess I can't cater for every situation.
Anyways, I'm reasonably happy with that, but I'm still stuck with the problem that I am updating the fields through using a button instead of a trigger. Keeping in mind that (only) one of the tabular samples will be dependent on the displayed staples, what trigger(s) should call the update procedure in order to ensure all of the samples are updated every time they need to be? I've been given a variety of suggestions by classmates and lecturers, including but not limited to, ON-POPULATE-DETAILS and POST_QUERY i think...
I haven't had time to experiment with it the last couple of days, but will be going back to it soon to try and figure it out.
thx
adam
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
