Wanted to use multiple Lookup in FlowField

sanjeevasawalesanjeevasawale Member Posts: 63
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. :)

Comments

  • Michael_SchumacherMichael_Schumacher Member Posts: 81
    edited 2005-09-05
    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 ;-)
    regards,
    Michael Schumacher

    oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
  • sanjeevasawalesanjeevasawale Member Posts: 63
    edited 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.
  • kinekine Member Posts: 12,562
    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.)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • sanjeevasawalesanjeevasawale Member Posts: 63
    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 ...
  • sanjeevasawalesanjeevasawale Member Posts: 63
    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 ...
  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • MagnoMagno Member Posts: 168
    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.
    There are no bugs, only random undocumented features...
    ---
    My Blog: http://NAV-Magno.be
  • Michael_SchumacherMichael_Schumacher Member Posts: 81
    I have another idea, which should work hopefully :wink:

    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:
    regards,
    Michael Schumacher

    oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
  • kinekine Member Posts: 12,562
    But this will not work on list form... =;
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Michael_SchumacherMichael_Schumacher Member Posts: 81
    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.
    regards,
    Michael Schumacher

    oh, what day is it? Backup day. OK, let's dump all data to /dev/nul ;-)
  • ajhvdbajhvdb Member Posts: 672
    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;
  • sanjeevasawalesanjeevasawale Member Posts: 63
    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.
  • RydenRyden Member Posts: 24
    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;
    
    --
    www.nabsolutions.se
  • rsaritzkyrsaritzky Member Posts: 469
    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!
    Ron
  • RocheTRocheT Member Posts: 7
    edited 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.
Sign In or Register to comment.