I don't think you can do this with u_where. You could retrieve the data, sort by doc_no then rev_no:descending and loop through discarding the ones which are the same doc_no as the previous doc_no. sort/e "TR_TBL", "DOC_NO,REV_NO:d" forentity "TR_TBL" if(v_doc_no = doc_no) discard "TR_TBL" else v_doc_no = doc_no endif endfor Alternatively, and depending on your database, you could make an updateable view on tr_tbl :- select * from tr_tbl where rev_no = (select max(rev_no) from tr_tbl t2 where t2.doc_no = tr_tbl.doc_no) And use this as the table within Uniface. Uniface does not do set based queries itself, and therefore cannot reference the max rev no, unless you had another table/view storing doc_no,max(rev_no)

forentity which includes discard or remocc do not work, as stated in the helpfile. Instead of the view you can use the following strategy using all the new features of read: At first, we read all records (MAXHITS=ALL), use a putlistitems command to get a list of DOC_NO then we do a sort/list with unique option we clear/e our entity and now collect the records DOC_NO by DOC_NO: We use forlist and for each DOC_NO, we start a retrieve/a with the order_by rev_no:descending in the read trigger, we use MAXHITS=1 to get only the highest version.

If you want to change out the read trigger (which I try to avoid) try read order by "DOC_NO,REV_NO:d" if($previous("DOC_NO") = DOC_NO) discard "$entname" (put $entname in great than less than, I can't get this daft software to show things in this form) endif This should cause it to throw away all the but the highest rev no of each document during the read phase.

from the days of uniface 5, there was a trick with the WHERE (not U_WHERE) string: not sure if the situation it is based still works with the database connectors of today under all circumstances because it depends that the WHERE string is placed at the very end of the generated SQL https://groups.google.com/d/msg/comp.soft-sys.app-builder.uniface/WtdpPYwCneA/OpbAbt5_uKIJ

I found the original listserver contribution from Dirk Willecke at: https://groups.google.com/d/msg/comp.soft-sys.app-builder.uniface/PYs34kQ7Dx8/UamEe-RqGa4J

FiresongKt said If you want to change out the read trigger (which I try to avoid) try read order by "DOC_NO,REV_NO:d" if($previous("DOC_NO") = DOC_NO) discard "$entname" (put $entname in great than less than, I can't get this daft software to show things in this form) endif This should cause it to throw away all the but the highest rev no of each document during the read phase.

Thank you for your quick reply.. I need more help until I am very new to uniface development. Actually what I need is this I have three tables GLBGTMM,GLBGTHM and GLBGTDM. all three tables are painted in the form. REV_NO is part of the PRIMARY KEYS is all three tables. Now in the user end user will need to update the records, to save those changes what I need to do is duplicate the current record by creating identical record and increase the rev no in all three tables MM table contains only one record per one doc number but other two will have multiple records, I need to change REV_NO of all those after user revised the values

Thank you for your quick reply.. I need more help until I am very new to uniface development. Actually what I need is this I have three tables GLBGTMM,GLBGTHM and GLBGTDM. all three tables are painted in the form. REV_NO is part of the PRIMARY KEYS is all three tables. Now in the user end user will need to update the records, to save those changes what I need to do is duplicate the current record by creating identical record and increase the rev no in all three tables MM table contains only one record per one doc number but other two will have multiple records, I need to change REV_NO of all those after user revised the values

Thank you for your quick reply.. I need more help until I am very new to uniface development. Actually what I need is this I have three tables GLBGTMM,GLBGTHM and GLBGTDM. all three tables are painted in the form. REV_NO is part of the PRIMARY KEYS is all three tables. Now in the user end user will need to update the records, to save those changes what I need to do is duplicate the current record by creating identical record and increase the rev no in all three tables MM table contains only one record per one doc number but other two will have multiple records, I need to change REV_NO of all those after user revised the values

So the rev_no should not be part of the primary key of the MM table, it should instead be data. This makes it simple. Paint the MM table outside the other two, and set their read triggers to u_where rev_no.table = rev_no.mmtable When it comes time to copy them, use the release/e command on the OTHER two tables, not the MM table. This will disconnect the records from the database, you can then set the rev_no of the MM table to rev_no+1 and the rev_no of the other two tables to match.

FiresongKt said So the rev_no should not be part of the primary key of the MM table, it should instead be data. This makes it simple. Paint the MM table outside the other two, and set their read triggers to u_where rev_no.table = rev_no.mmtable When it comes time to copy them, use the release/e command on the OTHER two tables, not the MM table. This will disconnect the records from the database, you can then set the rev_no of the MM table to rev_no+1 and the rev_no of the other two tables to match.

REV_NO is part of the all three table primary keys.... I need to duplicate the record(s) and increase the rev_no at the same time to save the revised data and to keep history data in the same table with REV_NO as updating factor.

well, you say that there is only one MM record per doc_no, so all rules about Normalisation (Peter CHEN) says the primary key is only this doc_no. But if you do not like changing the key, still the read u_where will work once you have collected all the MM records. On update, you create a new MM record to hold the new REV_NO, but to keep the "only one MM record" you have to delete the old record in the same transaction. Very error prone, isn't it?

When I did something like this, I just made sure the current records had an id of 0 (zero).