Setting filters on Option Field with FieldRef and RecRef

surrender
Member Posts: 5
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
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.
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.
0
Answers
-
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.0 -
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.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions