how to create a form to use two table data .

Hi All,

I have to create a form to use two tables like Item Ledger Entry and Warehouse Entry. but there are two fields above Item No. and lot No., and all the Fields from that two table are below. But condition is when i put data in above two fields i mean Item no and Lot no then all fields are retrieve automatically. so suggest me which type of form have to create and how, If any code need to write then please Explain.

Thanks in advanced

Hi Avinash,

If you use Ms Dynamics NAV 2009 Classic view, you can use subform concept. Get Item No. and Lot No. as variables in the main form. Attach two subform controls to main form

and set subform ID accordingly. eg. one for Item Ledger Entry and the other one for warehouse entry.

After that set subform view property as you want in each subform control.

Hi Avinash

There is possible ways available

  1. If you are using SQl database then create a new table and SQL View to select the records based on your condition and attache to the Table using “LinkedObject” property of table

  2. Make one table as Source Table and write code @ OnAfterGetRecord of Form trigger to get the records from second table, create variables and place it as form controls and assign the values from second table to the variables

Stiil lot ways available

Thanks

Jerome Marshal. J

Thanks Hansi,

but I have to see only that record which I put inside the item no and Lot No.I mean when i put the value inside that two above fields

Then records have to be filtered according to the above fields.

Thanks

Avinash

Thanks Hansi,

but I have to see only that record which I put inside the item no and Lot No.I mean when i put the value inside that two above fields

Then records have to be filtered according to the above fields.

Thanks

Avinash

Hi Avinash,

This one’s kind of tricky because you’re not using a source table in the primary form. That prevents you from being able to filter the sub-form records by using the SubFormLink property. So, you’ll have to do it the hard way. You’ll start with each of the sub-forms. Open them in Object Designer, and create a function called UpdateFilter. This function will have arguments that match the variable types you’re using for Item No. and Lot No, presumable Code20 and Code20. Within the function, you will set filters on these two fields using the values passed in. Once you have those functions completed, you’ll want to add a similar function to your main form. In this function, you will call the new functions to each of the subforms, passing the values of your local variables. You will want to call this function in the main form whenever you validate a value for either the Item No. or the Lot No.

You can get a good idea of what the function calls in your main function will look like by taking a look at the C/AL Symbol Menu (F5) while you’re in design mode on the main form. Look specifically for Controls that belong to the main form, and follow the columns to the right to get to the sub-form function definition. Use Paste Arguments and Apply to have NAV fill out the line of code that you need to call the function. Be sure to replace the argument template placeholders with the names of your two variables.

That’s a good place to start, I think.

Thanks George Love,

But now i bind the form to item table and link the subform to the main form

but when i run the form then first item no comes by default and below sub form table showing only that Item No record, as i want. but problem is, when i change the Item No and Lot No Then an Error comes that u want to rename this record .so if u no my problem then can u explain me in details because i am new in navision.

Reply me as soon as possible.

Thanks

Avinash

Hi Avinash,

Don’t attach main form into any table. thatz y itz asking to modify record when you change the item and Lot no. just create a form without attaching any table. and add two text boxes and two subform controls to that form. Then create two global variables named ItemNo. and LotNo. or give whatever name you like. Then set sourceExpr property of two text boxes. One text box for ItemNo. variable and LotNo. variable for the other text box. after that set TableRelation proporty.

example, set the TableRelation property of the text box which has ItemNo. as sourceExpr as follows.

TableRelation = Item.“No.”; this will enable you to lookup the Item Numbers.

Do the same to other text box also if there is a relationship. otherwise allow user to enter it manually.

after all set Subform ID property to of two subform controls as you want. One for Item ledger entry and the other one for Warehouse entry.

Next step is to set SubformView property. It is not simple, coz you dont have main table to attach. but you have two variables which gives ItemNo. and LotNo.

example :

“Item Ledger Entry”.“Item No.” WHERE (“Item No.”=FILTER(ItemNo.)) -

Hi Hansi,

But i am not able to set subformview property because of sourse table so it can be possible by write the code,

Thanks

Avinash

Hi Avinash, Yes, there is no Assit.Edit button in SUbformView property since there is no main form attached. but you can write the code manually.

Hi Hansi,

Let me know that code and tell me on which trigger i write this code.

send me soon please

Thanks

Avinash

Hi Avinash,

Ok do it in this way. As I said earlier there should be two text boxes which attached to ItemNo. and LotNo. variables.

after that again there should be two subform controls which set SubformID property to ‘Item Ledger Entries’ form and other one to ‘Warehouse Entries’ form.

Make sure that you have given names for these subforms. Im giving ItemLedgerEntries and WarehouseEntries

all these controls should be in your main form. don’t attached your main form into any table.

I will explain how to filter Item Ledger entries according to your Item No. Apply the same to Warehouse entries.

step 1 - create a function in ‘Item Ledger Entries’ form - 38 - give a name like GetFilters or whatever you like. then give a parameters

Parameter - codItemNo.

Step 2 - go to the function and write the code as follows,

**GetFilters(“codItemNo”:Code[20]) //**this is the funtion

SETRANGE(“Item No.”,“codItemNo.”);//filter to Item No.

CurrForm.UPDATE; //Update the form

Step 3 - go to the form which you created. write following code under OnValidate() trigger of text box which attached ItemNo. Variable

CurrForm.ItemLegerEntries.FORM.getFilters(“ItemNo.”);

{access currform property, then you can access all the controls inthe form. ‘ItemLegerEntries’ is the name of subform control which attached to Item ledger entries. through that you can access the form attached the subform control. In this form you have created a function call GetFilters. you can acces it in here. after that give the parameters as you want. here you want to filter your subforms according to Item no. variable. just give it as parameter. thatz all}

Step 4 - do the same thing for warehouse entries form also.

Thank you very much for all your Support it was really helpful.

I really wanted to reply you Earlier but was not having Internet connection so was unable to Reply you… but really it was very helpful.