Finance | Project Operations, Human Resources, ...

Filter data in a form having multiple data source

(0) Share

Report

Posted on by JasdeepSingh

192

I have created a form purpose of this form is to give availability of items present in consignment Warehouse(14) and in it I have created two filters First ItemID(EDT-ItemID) and second Dealer Code(EDT-CustAccount) and Form is having 3 Data sources InventTransferTable, InventTrasnferLine, InventDim.

I have 2 questions:

first, How do I filter the form to contain only records if items which are selected in transfer order line should have AvailablePhysical 1 or more.

Second, How to I make filters(ItemID, DealerCode ) work as intended. To filter records as per filter applied after clicking search button.

I have Overriden and applied query ranges in init and executeQuery methods of InventTransferTable(containing Dealercode) and InventTransferLine(containing ItemId) but not working as intended.

I have the same question (0)

All responses (7)

Answers (0)

Sort by

André Arnaud de Cal... 304,321 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi Jasdeep,

Do you need help on a customization or do you need to know how to perform it on the user interface?

Note that the available inventory is not part of the three tables you have now as data sources. The available on hand can be retrieved from the InventSum table.

Was this reply helpful? Yes No
JasdeepSingh 192 on at

Like (0)

Report
Copy link

Link copied!

So I will add InventSum for Available physical....and Can you help me with the query..?

I need to know how to perform it on user interface.

I need to get records of items which are transferred to consignment WH(14) from different warehouses using Transfer order functionality and are not returned back to initial warehouse i.e stock of items at WH 14.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report
Copy link

Link copied!

Hi Jasdeep,

I believe a SQL expert would be able to helps you on writing the query. I'll try to connect with my team, update you if i could do that....

Thanks,

Ajit Ranjan Dhal

Was this reply helpful? Yes No
André Arnaud de Cal... 304,321 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi Jasdeep,

What is the exact purpose of this form? If it is about showing the on-hand in consignment, then I wonder about the purpose of the InventTransfer tables. They are used for shipping goods from one warehouse to another.

In that case, you can better have a form like the on-hand item information, but then filtered on a certain warehouse.

Was this reply helpful? Yes No
JasdeepSingh 192 on at

Like (0)

Report
Copy link

Link copied!

Hi Andre,

I have done customizations in Transfer Order page(InventTransferOrder Form) for capturing consignment related information like TransferNumber, Purpose of consignment, Address, which are created in InventTransferTable. Refer Screenshot in question. Other fields are from InventTransferLine and InventDim. Now as suggested by you i have added inventSum Table and trying to make a query using AvailPhysical field of inventSum table.(if my approach is correct)

Purpose of this form as Name suggests "Consignment Stock" is to get the stock details of items at consignment warehouse which are sent for demo or exhibition purpose but are not returned back to the initial warehouses.

I need help in query in this form to get items which are not returned to initial warehouses after purpose is solved. i.e the items present at consignment WH at an instance.

Was this reply helpful? Yes No
André Arnaud de Cal... 304,321 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi Jasdeep,

Personally, I would avoid to customize this form when you want to know a certain status. It would then be better to look at inventory transfer lines or the related transfer transactions as you then have an overview directly on the items instead of the headers.

Can you tell how you currently track the Purpose of consignment? Are you using a new custom field or have you done a setup using inventory dimensions? Where and how are these details stored?

Was this reply helpful? Yes No
JasdeepSingh 192 on at

Like (0)

Report
Copy link

Link copied!

when i transfer any items using transfer order to a particular warehouse, I need to filter records of items which are serially controlled and are available at that instance in the destination warehouse.

Currently I applied range on InventLocationId field of InventDim and availPhysical field of InventSum, but when I create a transfer order and receive it and If I check the corresponding record of that transfer order in InventSum table ,value of availPhysical is not updated to 1. It remains blank.

I realized that various InventDim records are made for same transfer and the inventDim which joins the inventTransfertable and inventDim is showing the InventLocationId as source Warehouse and i have applied filter in inventLocationId as target Warehouse .

please help forgetting the right inventdim record

Was this reply helpful? Yes No