Question Using a query to update values.

[wa4qms]

Hi all, hoping all are doing well.
I have a query, freeform, that is built dynamically based of values set from a group of drop down values. This allows, much like excel, for the user to build their own query on the values they need to filter by. Works great.

However what I'd like to do now is have a button trigger that will update a field from a value the user has selected to a certain field.
So the query is open in the browser; I want to update column x in each record to value y.
Some Note:
I use a temp-table called tt-load to load in data from a .txt file.
So would something along the lines of
for each tt-load in browser x:
assign tt-load.field:screen-value = value Y

While code is more than likely not correct, is my thought process correct?
-Dennis-

 

[Osborne]

I am not quite sure, but I think it is just a matter of reading all the records, updating the fields then invoking a browse refresh.

A very old code example but think this is on the lines of what you are trying to achieve - just adapt to assign from a column value instead of a frame field value:

DEFINE TEMP-TABLE tt
    FIELD a AS INT
    FIELD b AS CHAR.

DEFINE BUTTON bt  LABEL "Modify".
DEFINE BUTTON btS LABEL "Save".

DEFINE QUERY qr FOR tt.
DEFINE BROWSE br QUERY qr DISPLAY tt.a tt.b WITH SIZE 60 BY 4 .

DEFINE FRAME f
    br bt
    tt.a SKIP tt.b btS WITH THREE-D SIDE-LABELS.

ON 'choose':U OF bt IN FRAME f
DO:
    RUN updateRecord.
END.

ON 'choose':U OF btS IN FRAME f DO:
    FOR EACH tt:
       ASSIGN tt.a = INPUT FRAME f tt.a
              tt.b = INPUT FRAME f tt.b.
    END.
    BROWSE br:REFRESH().
    CLEAR FRAME f.
END.

/* Create some test records */
CREATE tt. ASSIGN a = 1 b = 'first'.
CREATE tt. ASSIGN a = 2 b = 'second'.
CREATE tt. ASSIGN a = 3 b = 'third'.

OPEN QUERY qr FOR EACH tt.
ENABLE ALL EXCEPT tt.a tt.b WITH FRAME f.

WAIT-FOR CLOSE OF CURRENT-WINDOW.

PROCEDURE updateRecord:
    DISPLAY tt.a tt.b WITH FRAME f.
    ENABLE  tt.a tt.b WITH FRAME f.
END PROCEDURE.

 

[wa4qms]

Thanks for responding. I am trying not to have to do that as the actual query build is very long. There are maybe 9 different filters that are used and need to be tested. So, for example, I'd have to test each screen-value with something like "
assign
" and if x:screen-value <> ""
then tt-value = x:screen-value
else tt-value "

Can be done, but if any change is made to the filtering, it would then also have to be changed on the trigger.
Right now I have a message in the trigger that tells me how many records have been filtered.
I thinking of adding looking at the browser directly for the number of records.
Maybe something along the lines of for each row or temp-table in browse frame. Hmmmm, I'll try that later.
-Dennis-

 

[PatrickTingen]

If I read your response, I think you may have a partially dynamic query, one that uses or does not use fields from the frame. A fully dynamic query would look something along the example attached. Look in the choose event of the button

In this demo I just copied the records from the customer table to a temp-table, but you should look at this as "just" a way to populate the tt. You fill it based on your own criteria. (run against sports database)

 

[wa4qms]

Thanks for the info, but I already have a very complicated multilevel search procedure written. What I was trying to do is to use the query and reset on of the fields in the querred temp-table to a pre-defined value. So all the rows in the frame are results of the query, but the question then becomes how to set a value within the frame to what it needs to be. So my next part will be to 'grab' the frame value of the first row and see if it is what I expect. If so, I can than just count the rows in the query and that will let me know if I'm only looking at the records I want to. In this one temp-table there are over 38,000 rows, but I can filter it down to 6. If in fact the first row displayed is in fact the first record of the query, and if I walk thur rows counting them, and come up with 6, I have the answer to my problem.

 

[PatrickTingen]

I am not fully sure what you are trying to do but it looks as if you are making your life extremely complicated by mixing UI and logic. You use the screen-value of your filters in the query, if I am correct. By building a dynamic query, using the actual value from the filters (not a reference to the screen-value), you can then use that same query to update the fields you want.

I adopted the program above to reflect what I mean, check to see if you can use the ideas in it.

 

[wa4qms]

I believe including the actual query build code is in order. That way you can see just how complicated the query is. While I'm uploading this as a .p, in fact it is an internal procedure within the .w program. In short, is scans all the 'filters' , which are drop-downs, and builds and executes the query. Because I am using pre-defined values, either hardcoded of a result of a build, the user is not allow to override those values but only select what may be in the list. So, once the query is executed, I should, via screen-values, be able to update the field on each row of the query, but it is selecting the screen-value that is the issues. When I get a chance I am going to test my theory of being able to 'select' the first row of the query, and then count the number of row. If that is successful. problem solved.

 

[PatrickTingen]

This might be a language issue (English is not my native tongue) but I think I don't understand what it is that you want to achieve. Your query looks good; you build a truly dynamic query and then attach it to the browse. So your user selects a couple of filters, you build the query, attach it to the browse and open it. The user then sees all the records she selected.

And then what?

I assume she wants to update all the records she sees, to set field X to value Y for all records in the browse. Am I right? If so, then I don't know what you mean with the last two sentences of your last post:

So, once the query is executed, I should, via screen-values, be able to update the field on each row of the query, but it is selecting the screen-value that is the issues. When I get a chance I am going to test my theory of being able to 'select' the first row of the query, and then count the number of row. If that is successful. problem solved.

How do you want the user to update the fields? Why do you need to update the field via screen-values? Should she use the same dropdowns that were used for the filter? Why is "selecting the screen-value" an issue?

If your user should be able to set field X to Y, why not open a new window where she can set the values to what she wants. You can pass on the query as a parameter to this program.

 

[wa4qms]

On the ribbon bar there is a drop down of 5 or so values.. The user would select one, hit a button that would walk thru records and apply that value to the a field in the column for each row on the current browse. You can determine row ID value of a row in a browse and you can do a trigger that will advance to the row. I've already written that for a different trigger. What may not be obvious is that a query when created has its own unique ID whose value you can retrieve. This allows you to position the 'active' row dynamically. So if my current row is 1234, my current button applies the change to the screen-value of that row then makes the next row the focused row ID 1235. The question then are the IDs of the current browse / query in sequence? If my theory is correct and they are in sequence, it is a matter of some simple code: apply valve to screen-value, next row.