Filter existing Form onLookup

Business Central/NAV User Forum
1

Hello Everybody,

I have another trouble. Is there possible to filter existing Form when I click “LookUp Button” ? I have table MyTable with fields such as Customer No., Participant No. The “Customer No.” has TableRelation set to Customer.“No.” and "Participant.“No.” has TableRelation set to Contact.“No.”. Now I have to make functionality that filter the Contact List and leave only records that are connected with “Customer No.” in table Contact Business Relation (5054). Is there any way to do this or do I have to create my own table ?

It will be great if you help me :slight_smile:

2

Hi, have you looked at the SourceTableView on the properties for the form? This may be a good starting point for you.

3

Hey Kamil,

Are you saying that when you are starting with a list of Customers and that you want to open a form/list from that displays a second list of contacts but only those that are related to the customer in focus on your first form?

t

4

TonyH - something like that. I have to filter “Contact List” (because this form is call when I click on LookUp Button) and leave only those records that match those rules:

“Contact Business Relation”.“Contact No.” = Mytable.“Participant No.”;

and

“Contact Business Relation”.“No.” = Mytable.“Customer No.”;

Of course Contact.“No.” = Mytable.“Participant No.”;

Did I clear anything with those explanations? Is it possible without creating extra Form?

5

On the field on your table you can set a conditional table relation that will filter the “Contact List” based on the “Customer No.” of the current record.

Got to the source table of the form you are working with, find the “Contact No.” and look at the “Table Relation” property, add a condition there and see if that resolves your issue.

t

6

Unfortunately it doesn’t. I was hoping that it can be done from C/AL level in onLookup trigger. Is there way to create sonething like that:
Create join many-to-many of tables Customer and Contact and then fetch those “No.” out that matches rules I mentiond above ?

7

You should attempt to make changes to the system as close to the datasource as possible… i.e. if you can do it on the table you should.

In the example image below I created a new table with two field “Customer No.” and “Contact No.”, on the “Contact No.” I have added a table relationship to it.

4667.relationships.JPG
4667.relationships.JPG989×716 80.6 KB

8

He seems to be doing the DEV II exercises.

Kamil, for an example of how this works, open the Customer Card from the Classic client, and turn on the debugger. Click the lookup button on the “Primary Contact No.” field and step through the code to see how this works in the standard application. Figure out the table relationship on the table level by opening the Customer table from the table designer, and looking at the table relationship definition.

The table has a straight tablerelationship to the Contact table, and the validation and lookup is handled in the OnValidate and OnLookup triggers of the TABLE, NOT THE FORM.

So use that example to define the relationship and validation/lookup code in your participant code field.

9

Thanks for your help guys !! I override OnLookup trigger and everything worked excellent !:slight_smile: But unfortunately there is one more obstacle. I can’t put chosen Contact (its code) into my Field (“Participane Contact No.”) ? I use this code

IF FORM.RUNMODAL(5052, Contact) = ACTION::LookupOK THEN BEGIN

“Participant Contact No.” := Contact.“No.”;

VALIDATE(“Participant Contact No.”);

END;

“Contact” is a variable that contains results from filtering “Contact List”. And when I click OK on this FORM, nothing happen.

My friend suggest that I could use a Form variable and use function SETTABLEVIEW. I did it, but in this scenerio I don’t have “OK” Button on FORM. Code is below:

ContactForm.SETTABLEVIEW(Contact);

ContactForm.SETRECORD(Contact);

IF ContactForm.RUNMODAL = ACTION::LookupOK THEN BEGIN

ContactForm.GETRECORD(Contact);

“Participant Contact No.” := Contact.“No.”;

VALIDATE(“Participant Contact No.”);

END;

Do you know why is this happening?

10

Thanks for your help guys !! I override OnLookup trigger and everything worked excellent !:slight_smile: But unfortunately there is one more obstacle. I can’t put chosen Contact (its code) into my Field (“Participane Contact No.”) ? I use this code

IF FORM.RUNMODAL(5052, Contact) = ACTION::LookupOK THEN BEGIN

“Participant Contact No.” := Contact.“No.”;

VALIDATE(“Participant Contact No.”);

END;

“Contact” is a variable that contains results from filtering “Contact List”. And when I click OK on this FORM, nothing happen.

My friend suggest that I could use a Form variable and use function SETTABLEVIEW. I did it, but in this scenerio I don’t have “OK” Button on FORM. Code is below:

ContactForm.SETTABLEVIEW(Contact);

ContactForm.SETRECORD(Contact);

IF ContactForm.RUNMODAL = ACTION::LookupOK THEN BEGIN

ContactForm.GETRECORD(Contact);

“Participant Contact No.” := Contact.“No.”;

VALIDATE(“Participant Contact No.”);

END;

Do you know why is this happening?

11

Its happening bcoz you have override default lookup of table.

12

Solution to this i set ContactForm.LOOKUPMODE(TRUE);
This works well, buttons OK and CANCEL exist on this FORM. But still the “No.” and “Name” fields aren’t filled with values from Contact Record.

13

Need to validate the contact field or else need to write a code on contact field to fill the values

14

Amol I did this, still does not work: I used this code

IF ContactForm.RUNMODAL = ACTION::LookupOK THEN BEGIN
ContactForm.GETRECORD(Contact);
“Participant Contact No.” := Contact.“No.”;
VALIDATE(“Participant Contact No.”, Contact.“No.”);
END;

I even do this in onValidate trigger:

IF Contact.GET(“Participant Contact No.”) THEN BEGIN
“Participant Contact No.” := Contact.“No.”;
“Participant Name” := Contact.Name;
END;

Debugger shows that those fields have value, but there is no value on FORM.

15

Suddenly everything start working ! :slight_smile: I don’t know why, let’s call it magic :slight_smile:

Thanks for help everybody !! :slight_smile:

16

There’s a tiny little problem here though… When you have a contact that is related to the customer, you also need to make sure there is the proper contact business relation, and this code does not do any of that.

17

What I pasted on previous post is not the entire code. This is the whole code in my onLookUp trigger:

CLEAR(ContactForm);

ContBusRel.SETFILTER(“No.”, “Bill-to Customer No.”);

IF ContBusRel.FINDSET THEN BEGIN

REPEAT

Contact.SETFILTER(“No.”, ContBusRel.“Contact No.”);

UNTIL ContBusRel.NEXT = 0;

END;

ContactForm.SETTABLEVIEW(Contact);

ContactForm.SETRECORD(Contact);

ContactForm.LOOKUPMODE(TRUE);

IF ContactForm.RUNMODAL = ACTION::LookupOK THEN BEGIN

ContactForm.GETRECORD(Contact);

“Participant Contact No.” := Contact.“No.”;

VALIDATE(“Participant Contact No.”, Contact.“No.”);

END;

18

No you are not getting the relationship right. You need to open the Customer table in design mode, and look at the code behind the “Primary Contact No.” field. This will tell you how the relationship between the customer table, the contact business relation table and the contact table. It’s not just the customer number, there are also other fields that are part of the relationship, and you need to take care of all of them.

Also, the way you have it programmed, your loop on the ContBusRel variable has no meaning. Don’t program a loop if you are not going to utilize it.

19

Den thank you for advise. I looked on Customer table and change my code. I hope I’m getting the relationship right. One more time thanks for help ! :slight_smile: