get records based on different table from one filter is set

Morgan · 2004-10-21

Dear friends,

I explain in a simple way, I have 3 tables:
-Edition (Code, Designation,...) // Primary key is Code
-Page (Code, Designation,...) // Primary key is Code
-Edition/Page Assignment (Edition Code, Page Code,...) // Primary key is Edition Code, Page Code

I set filter on "Edition/Page Assignment" in order to only have pages for one Edition.
-> EditionRec is the Record resulting of EditionRec.SETFILTER(Edition Code,'my_edition_code');

I want to open the form "List of Pages" based on Table "Page" but filtering the records in order to only get the list of pages contained in EditionRec (in a specific edition).
=> problem is : in FORM.RUNMODAL(formID,RECORD) , RECORD is based on the source table, in my case EditionRec is not based on the form source table.

I don't know if it is possible to get only some records based on table "Page" when you have a list of page code for example....

PS:For one edition, it is possible to have sereval times the same page with different Headers and sub-headers

Thanks!

Waldo · 2004-10-21

Well, there are several ways to handle this kind of problem.
Here are some:

1.
You can create some kind of functionality that concatenates the filter you need. The code can be like this:

txtFilter := '';
IF EditionRec.FIND('-') THEN
  REPEAT
    txtFilter := txtFilter + EditionRec.Code + '|'
  UNTIL EditionRec.NEXT = 0;

//To remove the last '|'
IF txtFilter <> '' THEN
  txtFilter := COPYSTR(txtFilter,1,STRLEN(txtFilter)-1);

This way you have your filter, which you can use like this to open your form:

PageRec.SETFILTER(Code,txtFilter);
FORM.RUNMODAL(FormID,PageRec);

2.
If it is not necessary to edit in the form you want to open, just create some LookUp-Flowfields in the Edition/Page-table that looks up the descriptions you want from the Edition table and the Page table. Then, you can base your list-form on the Edition/Page-table, and just use the filter on EditionRec (GETFILTER(Code)).

I hope this is clear, and this can help you ...

Regards.

Morgan · 2004-10-22

Great, first solution works well !!

Thanks a lot Waldo

SteveO · 2004-10-22

COPYFILTER would have also worked.

Record.COPYFILTER(FromField, ToRecord.ToField)

This will copy the filter from the first specified Table.Field to the second specified Table.Field.

A lot simpler.

Waldo · 2004-10-22

I think you're wrong.

The filter is on "Edition Code" of the Edition/Page Assignment table, and he wants to filter the Page table on the code field.

But probably you just interpreted my code, which indeed you can replace by COPYFILTER. Morgan needed not exactly that code, but had to program something likewise ...

I think I should have written the following to avoid the mix-up:

txtFilter := ''; 
IF EditionPageRec.FIND('-') THEN 
  REPEAT 
    txtFilter := txtFilter + EditionPageRec."Page Code" + '|' 
  UNTIL EditionPageRec.NEXT = 0; 

//To remove the last '|' 
IF txtFilter <> '' THEN 
  txtFilter := COPYSTR(txtFilter,1,STRLEN(txtFilter)-1);

where EditionPageRec is filtered on "Edition Code".

Regards.

Morgan · 2004-10-22

You're right Waldo, COPYFILTER coun't work because on Edition/Page Table, filter is appy on edition code which is not the same as the page code in Page table ....

But your solution runs well!

SteveO · 2004-11-29

Sorry I misunderstood the question altogether :?
Sorry for the confusion.

Howdy, Stranger!

Quick Links

Categories

get records based on different table from one filter is set

Comments