Wanted to use multiple Lookup in FlowField

sanjeevasawale · 2005-09-05

Hi Friends.

I have G/L Entry Table which has "Source Type" as 'Customer'/'Vendor' and "Source No" contains Customer Code or Vendor Code based on Source Type. Now I have added one more field called "Party Name" which is Text (30). Using this field I changed FieldClass = FlowField and put CalcFormula as "Lookup(Customer.Name WHERE (No.=FIELD(Source No.)))". This shows me Names from Customer table in above field if Source Type is "Customer" and blank if Source Type is "Vendor".

Now I wanted some other way (might be using code) which will bring Vendor's Name from Vendor Master. ](*,)

In short, the way by which we can define Lookup thru design mode can be coded in table. :?:

Thx in Advance.

Michael_Schumacher · 2005-09-05

sanjeevasawale wrote:

In short, the way by which we can define Lookup thru design mode can be coded in table. :?:

I would try it like this in CalcFormula

if "Source Type" = "Source Type"::Customer then
  Lookup(Customer.Name WHERE (No.=FIELD(Source No.)))
else
  Lookup(Vendor.Name WHERE (No.=FIELD(Source No.)));

but as kine says some postings later, it wouldn't work, sorry for that, it was just an idea, which i never tried at home ;-)

sanjeevasawale · 2005-09-05

Why don't you try it like this in CalcFormula

if "Source Type" = "Source Type"::Customer then
  Lookup(Customer.Name WHERE (No.=FIELD(Source No.)))
else
  Lookup(Vendor.Name WHERE (No.=FIELD(Source No.)));

Thx for Reply, I put above codes in "Party Name".OnLookup but when I compile it gives me error "You have specified an unknown variable'.....Lookup....Define the variable under "Global C/AL symbols"

Also let me know what validity code are require to put in OnValid trigger.

Plz help.

kine · 2005-09-05

Flowfields cannot be conditioned. There is no way how to do it in one field by using standard flowfield. You must use another way (OnValidate the Source No. etc.)

sanjeevasawale · 2005-09-05

kine wrote:

Flowfields cannot be conditioned. There is no way how to do it in one field by using standard flowfield. You must use another way (OnValidate the Source No. etc.)

Kine, Could you plz explain me further how to use another way ...

sanjeevasawale · 2005-09-05

kine wrote:

Flowfields cannot be conditioned. There is no way how to do it in one field by using standard flowfield. You must use another way (OnValidate the Source No. etc.)

Kine, Could you plz explain me further how to use another way ...

kine · 2005-09-05

The field will be normal field, in "Source No." OnValidate trigger you add code, which take the Name of the Customer/Vendor and insert it into your field. The name will be fixed, will be saved in your table and will not change if you rename the Customer/Vendor (this is disadvantage if you want to have up-to-date values there).

Or another way is, but only if you do not need to filter this field or sort by this field, on the form, where you want to show this name, do not use table field but some global variable which you will fill by the name in C/AL code called from OnAfterGetRecord or OnAfterGetCurrRecord...

Magno · 2005-09-05

The only possible way, is to create a textfield with the max length of vendor name/customer name.

On validate of the field source No.

write following code:

CASE "Source Type" OF
  "Source Type"::Vendor:
    BEGIN
      recVendor.RESET;
      if recVendor.get("Source No.") then
         "New textfield" := recVendor.name;
    END;
  "Source Type"::Customer:
    BEGIN
      Customer.RESET;
      if Customer.get("Source No.") then
         "New textfield" := recVendor.name;
    END;
END;

Maybe, you can put this code in a method, then, you can call this method from Source type and No.
But be sure to check if both fields are filled in then.
If you place the new field not editable, it is exactly as a flowfield, except for the lookup. there you can write something like this. or just leave it as-is.

Hope this helps you.

Michael_Schumacher · 2005-09-05

I have another idea, which should work hopefully

put a second flowfield in for vendorname and on the form place the textboxes for them exactly over each other.
in the on_after_get_record and "Source Type".on_validate triggers put this

IF "source type"="source type"::customer then begin
  customername.visible:=true;
  vendorname.visible:=false;
end else begin
  customername.visible:=false;
  vendorname.visible:=true;
end;

so the corresponding field will be visible when you open the form and it will change if you change the sourcetype.

I think this should work but I didn't hav tried it either.

sorry for inconvenience caused by my last posting :oops:

kine · 2005-09-05

But this will not work on list form... =;

Michael_Schumacher · 2005-09-05

kine wrote:

But this will not work on list form... =;

on list form you put this fields side by side and only one of them will be visible.

Ah, Ok, then only entries of the same type as the current record will be correct.

In a list form there must be both fields visible to see everywhere correct things. and you can play with colours which show the correct field... this should work for each entry in list.... as far as I remember.

ajhvdb · 2005-09-05

Create a function GetSource with the logic you want (if the else) that returns a string.

Create a new field on the form
SourceExpr = GetSource

Note:
You cannot filter on this field.

Another option is to use the onformat event of Source Type.
Text := GetSource;

sanjeevasawale · 2005-09-06

Hey Friends,

I think some of us diverted 2 different track of putting codes in form. I wanted to put those code in the table itself. I triied to replace by getting Name from Customer and Vendor and replacing in "Party Name" but it is time consuming as well as it updates table field. WHere as we should find a way where can we use navision's existing functionality which show contents of field from different table but don't store values in that field.

I am trying my best, but friends needs help/suggestions from u also.

So plz find a way 2 sort this issue.

Ryden · 2014-09-11

This is how I solved it for a list:

In the list I added a TextBox with SourceExpression set to a global variable gvName and filled the variable with data in OnAfterGetRecord
In this code Table1 has precedence over Table2, but you might set any condition to choose between different tables.

IF Table1.GET("No.") THEN BEGIN
  gvName := Table1.Name;
END ELSE BEGIN
  Table2.RESET;
  Table2.SETCURRENTKEY(Name);
  Table2.SETRANGE("No.", "No.");
  IF Table2.FINDFIRST THEN BEGIN
    gvName := table2.Name;
  END ELSE BEGIN
    gvName :='';
  END;
END;

rsaritzky · 2014-09-11

sanjeevasawale wrote:

Hi Friends.

I have G/L Entry Table which has "Source Type" as 'Customer'/'Vendor' and "Source No" contains Customer Code or Vendor Code based on Source Type. Now I have added one more field called "Party Name" which is Text (30). Using this field I changed FieldClass = FlowField and put CalcFormula as "Lookup(Customer.Name WHERE (No.=FIELD(Source No.)))". This shows me Names from Customer table in above field if Source Type is "Customer" and blank if Source Type is "Vendor".

Now I wanted some other way (might be using code) which will bring Vendor's Name from Vendor Master. ](*,)

In short, the way by which we can define Lookup thru design mode can be coded in table. :?:

Thx in Advance.

There is a way to do this (from a functional standpoint) using SQL Views. For this example, let's call the table "CustVend"
Other Assumptions:
"Source Type" is the standard NAV field, so Source Type "Customer" has integer value 1 and "Vendor" has integer value 2.

Create a SQL View and a Linked table that includes both Customer and Vendor Names. The view is

CREATE VIEW [CompanyName$CustVend] AS
SELECT
1 as [Source Type], c.[No_],c.[Name],c.[Name 2]
FROM
[CompanyName$Customer] c

UNION

SELECT
2 as [Source Type], v.[No_], v.[Name], v.[Name 2]
FROM [CompanyName$Vendor] v

NOTE: With all SQL linked tables, you must create a view for each company in your database - just replace "CompanyName" above with each of your actual company names.

Then, In NAV create a new table with the same name as the view (e.g. CustVend) (LinkedObject property = Yes) with just fields
Source Type (Option) OptionStrung = ' ,Customer,Vendor'
No. (Code 20)
Name (Text 50)
Name 2 (Text 50)

NOTE: The table name MUST match the view name in SQL

Now you can create your flowfield to link to one table (the new CustVend Table) After the above, change your CalcFormula in your "Party Name" field in GL Entry to read:

Lookup(CustVend.Name WHERE (Source Type = FIELD(Source Type), No.=FIELD(Source No.)))"

There are some potential drawbacks to this approach. Views must be maintained for each company name, and you may have to DROP all the views for certain maintenance functions in NAV (like restoring from a backup), then recreating the views.

Another option would be to just add the field "Party Name" as a non-flowfield and add some code to the "GenJnlLineTOGenLedgEntry" function in codeunit 10201 "Transfer Custom Fields" to populate the proper name field. I don't know if you are a developer that has access to modifying codeunits, so I won't do that here. But if you are interested, reply to this thread and I'll help.

Good luck!

RocheT · 2017-10-04

Hi I have a workaround for this,

Customer Record(Customer)
Vendor Record(Vendor)
CustomerName Text(50)
VendorName Text(50)

IF "Source Type" = "Source Type"::Customer THEN
IF Customer.GET("Source No.") THEN
CustomerName := Customer.Name;
END
ELSE BEGIN
IF Vendor.GET("Source No.") THEN
VendorName := Vendor.Name;
END;

Just put this code in OnAfterGetRecord of the Page that you want to show the CustomerName/VendorName instead of using Lookup in Table.

Wanted to use multiple Lookup in FlowField

Comments

Categories