show data in grid from SalesTable and PurchTable

(0) Share

Report

Posted on by Community Member

I need to show sales orders and purchase orders in the same grid on a form.

The columns on the grid will be similar fields from SalesTable and PurchTable (Eg: Order#, Mode of delivery, Delivery Terms, Carrier etc)

There will be a check box next to the order#. User will pick what ever orders they want by clicking the check box. User will then click a button to process those orders.

I can not join SalesTable and PurchTable and have them as form datasource. (Our data for Sales orders and purchase orders are not inter company orders and are not related. Sales orders and purchase orders are seperate orders)

Should i create a tempTable and use that as the form data source and load the sales orders and purchase orders into the tempTable?

I also need to filter which sales orders and purchase orders show on the form.

any ideas? thanks.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

Suggested answer

Martin Dráb 239,029 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Yes, using a temporary table is a viable solution. Nevertheless you could also create a union query, which would be better from performance perspective.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Create a view using union query as @Martin Says.

this is a guide.

msdn.microsoft.com/.../cc605991.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

thanks Martin. I thought of union query but msdn documentation says that with union query, records can not be updated, data can not be sorted on second data source etc.

will i be able to get the datasource of the selected records and do the update, sorting etc.

please let me know if there is a existing Form in AX 2009 that i can use as example or if there is a link that has a example. thanks

Was this reply helpful? Yes No
Martin Dráb 239,029 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You didn't say anything about updating these records directly in the grid, therefore I obviously couldn't know about such a requirement. If this is a must, you have to use the other suggested solution.

Sorting works on union queries, you just have to add ranges to the first data source.

Was this reply helpful? Yes No
dolee 11,279 on at

Like (0)

Report
Copy link

Link copied!

Hi,

I understand this goes against your original requirement, but wouldn't a multi-tabbed form easier to use? (One tab shows PO, and the other show SO.)

Was this reply helpful? Yes No
Hariharans87 36 on at

Like (0)

Report
Copy link

Link copied!

I am not sure about AX 2009. For sorting and filtering, you can use view. If it is simple status update based on the marked records then you can update in the process button code side.

Please explain me detail about your update process.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

the records do not have to be updated directly on the grid.

I need to show sales orders and purchase orders in the same grid on a form.

The columns on the grid will be similar fields from SalesTable and PurchTable (Eg: Order#, Mode of delivery, Delivery Terms, Carrier etc)

There will be a check box next to the order#. User will pick what ever orders they want by clicking the check box. User will then click a button to process those orders.

So, i need to loop through the records that have been selected by clicking the check box and update those records in SalesTable and PurchTable.

will i be able to get the datasource of the selected records, so that i can update those records when the process button is clicked?

I also need to filter and sort the records that i want to show in the grid.

should i go with union query or tempTable?

Was this reply helpful? Yes No
Verified answer

Martin Dráb 239,029 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

In that case, my first reply is still completely valid - both options are possible.

Choose what makes better sense for your specific scenario, taking into account all requirements and restrictions.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

thanks a lot Martin. I appreciate your ideas.

I have 6 fields that are common from Sales and Purch for which union query will work. But I also have 10 fields that would have different data for Sales and Purch.

I don't want to have 10 display fields on the grid.

I am thinking of going with tempTable.

Was this reply helpful? Yes No