One-to-many table relation

illi4

Dear colleagues,

Could you please advice if it is possible somehow to do one-to-many table relation in Nav (so, to get information from several tables in OnLookup)? I think that it is not possible, but I am not sure. Right now I can see only one way - to create temporary table and display it on OnLookup trigger.

Thanks.

MBerger

And how do you expect to later know which table the original data came from ?

DenSter

illi4 wrote:

Could you please advice if it is possible somehow to do one-to-many table relation in Nav (so, to get information from several tables in OnLookup)? I think that it is not possible, but I am not sure. Right now I can see only one way - to create temporary table and display it on OnLookup trigger.

The term "one-to-many" refers to a table relationship where ONE record in a table can be related to MANY records in another table, it has nothing to do with the number of tables that are involved in the table relationship. An example is the Item Ledger Entry table, which has a field called "Item No." that has a table relationship to the Item table. One record in the Item table can be related to many records in the Item Ledger Entry table.

Are you talking about a field that works similar to the No. field on the sales line for instance, where the relationship is different based on the value of another field (the Type field in that case)?

matttrax

If you want your lookup to function on multiple tables, the field should have a conditional table relation. So you'd set another, probably Option, field to a vale (like Customer, Vendor, Bank Account). The actual field that you are performing the lookup on would have this conditional relation based on the other field.

Otherwise, as was pointed out, you will have no idea where the data came from and may have data issues later on.

Savatage

Or perhaps Like what I added to my customer Ledger Entries "Document No."
Depending on the "Document Type" the "Document No" Lookup goes to a different form.
Either (Credit , Invoice or Payment)

OnLookup(VAR Text : Text[1024];) : Boolean
CASE "Document Type" OF "Document Type"::"Credit Memo":
 BEGIN
    CLEAR(PostedCreditForm);
    PostedCreditInv.SETFILTER(PostedCreditInv."No.","Document No.");
    PostedCreditForm.LOOKUPMODE(TRUE );
    PostedCreditForm.SETTABLEVIEW(PostedCreditInv);
    PostedCreditForm.RUNMODAL;
 END
END;
CASE "Document Type" OF "Document Type"::Invoice:
 BEGIN
    CLEAR(PostedSalesForm);
    PostedSalesInv.SETFILTER(PostedSalesInv."No.","Document No.");
    PostedSalesForm.LOOKUPMODE(TRUE );
    PostedSalesForm.SETTABLEVIEW(PostedSalesInv);
    PostedSalesForm.RUNMODAL;
 END
END;
CASE "Document Type" OF "Document Type"::Payment:
 BEGIN
    CLEAR(PostedPaymentForm);
    PostedPaymentInv.SETFILTER(PostedPaymentInv."No.","Document No.");
    PostedPaymentForm.LOOKUPMODE(TRUE );
    PostedPaymentForm.SETTABLEVIEW(PostedPaymentInv);
    PostedPaymentForm.RUNMODAL;
 END
END;

illi4

Hi all,

Sorry for the late reply. Yes, maybe 'one-to many relationship' is not appropriate definition.
The exact thing that I need to do is to combine records from Sales Header and Service Header, so, user can select Sales or Purchase Order in one place without choosing a document type at all. This field will be used as the information, and users do not want to search if they need to choose Service or Sales Order. That's the thing. Complex relationship depending of the other field value won't work for such case

PeterD

You can create a new table for that and add all sales headers and service headers with it's type. That way you can display this table to the user. When a record is selected you know where it comes from and open the right document.

illi4

Thank you for the advice. I actually created new temporary table on the form and put there sales and service headers.