Setting filters on Option Field with FieldRef and RecRef

surrender · 2018-05-23

Hello Community,

First of all, I am new in this Forum, so please forgive me if I do not perfectly consider all Community rules - just tell me and i will for sure take care for the next time.

Now, my Question - I am also quite new to Record and Field References.
What I am trying to do is to call a Method in a table where based on the Field "Partner Type" of the Table, the RecRef should OPEN the Customer, Vendor, GL Ledger Entries etc.
There I need to apply some filters on specific fields - which to my luck - have all the same Field IDs. So what i do i something like

CASE "Partner Type" OF 
  "Partner Type"::Customer:
    TransTableRef.OPEN(21);
  "Partner Type"::Vendor:
    TransTableRef.OPEN(25);
  "Partner Type"::"Bank Account":
    TransTableRef.OPEN(271); 
  "Partner Type"::"G/L Account":
    TransTableRef.OPEN(17);
END; 

TransFieldRef := TransTableRef.FIELD(3); //Customer No / Vendor No...
TransFieldRef.SETRANGE("Partner No.");
TransFieldRef := TransTableRef.FIELD(5); //Document Type
TansFieldRef.SETFILTER('%1|%2', "Document Type"  = "Document Type"::"Payment", "Document Type"::"" ) and so on  

next Steps would be to GETVIEW and open a Page with the saved view / recordset of the corresponding table

That brings me to my 2 Questions:
1) Can I put several filters on the same FieldRef just by changing the Field it points to, but without losing the Filter i set in the previous step?
So after these 2 commands i would like to have a filter on lets say Customer No = Partner No AND Document Type = Payment | ""
2) How can i perform the second filter on Document Type taking into consideration that it is an Option field?
Normally i would have to write something like Customer."Document Type" = "Document Type"::Payment (but in my case it can be Customer, Vendor, GL etc.

joerg_renz · 2018-05-25

Hello,
I have created two tables to "rebuild" table relations to get some data from different tables (in my case, to get personal data for one contact from different tables).

My first table have (among other fields) a field "Table No." and a field "Linked Table No.".
The second table have these fields also and additional "From Field No." and "To Field No.".

FeldRelation - Record - my second table
DataHierarchy2 - Record - my first table
ViewExpression - Text
FldRef - FieldRef
RecRef2 - RecordRef (opened with "Table No." from DataHierarchy2)


          FeldRelation.SETRANGE("Table No.",DataHierarchy2."Table No.");
          FeldRelation.SETRANGE("Linked Table No.",DataHierarchy2."Linked Table No.");
          CLEAR(ViewExpression);
          IF FeldRelation.FINDSET THEN BEGIN
            REPEAT
              IF (FeldRelation."From Field No." <> 0) AND (FeldRelation."To Field No." <> 0) THEN BEGIN
                FldRef := RecRef2.FIELD(FeldRelation."From Field No.");
                FieldValue := FORMAT(FldRef.VALUE);
                IF ViewExpression = '' THEN
                  ViewExpression := GetFieldCaption(FeldRelation."Linked Table No.",FeldRelation."To Field No.") + STRSUBSTNO('=FILTER(%1)',FieldValue)
                ELSE
                  ViewExpression := ViewExpression + ',' + GetFieldCaption(FeldRelation."Linked Table No.",FeldRelation."To Field No.") + STRSUBSTNO('=FILTER(%1)',FieldValue);
              END;
            UNTIL FeldRelation.NEXT = 0;
            IF ViewExpression <> '' THEN
              ViewExpression := STRSUBSTNO('WHERE(%1)',ViewExpression);
          END;

Function GetFieldCaption(TableNo : Integer;FieldNo : Integer)

IF Field.GET(TableNo,FieldNo) THEN
  EXIT(Field."Field Caption");
EXIT('');

After that program code, you have a text in var "ViewExpression".
With this var, you can open a second RecordRef for table "Linked Table No." and set a filter:

RecRef.open("Linked Table No.");
RecRef.setview(ViewExpression);

if you want to filter multiple values for one field, you have to modify the generation for "ViewExpression".

I hope, this helps.

You can also take a look at table 5303.
There are different functions (like "UpdateFilterExpression"), which uses RecordRefs and FieldRefs.

Mark_Smart · 2018-05-25

There are also some handy standard functions. Take a look how options values are validates in the field Default Value in table 8619, Config. Template Line. These can allow you to convert/validate text values into option values for a given fieldref.

Howdy, Stranger!

Quick Links

Categories

Setting filters on Option Field with FieldRef and RecRef

Answers