Question

How to use IFS field with NULL SQL Column in data source?

  • 23 September 2021
  • 2 replies
  • 344 views

Userlevel 2
Badge +6

Hi,

I’d like to create a lobby element with a field which has no SQL column. The column represents the projected quantity of an item, and can be found in Inventory Part Availability Planning > Availability Check per Day. Ideally, I would use this column to show a list of all items which are projected to have a negative quantity in the next two weeks. However, the column is a calculated aggregate value, where on each date the demand quantity is subtracted and the supply quantity is added.

Is there a way of storing the projected value as a custom field? or perhaps calculating it within a SQL query for a data source?

 

Availability Check per Day

Thank you in advance.


This topic has been closed for comments

2 replies

Userlevel 5
Badge +13

Hi @washton,

You can usually create a custom field, but based on the LU under consideration it’s not possible to create a customer field. Due to the below reason.

 


You can embed the logic, to the data source designer of the lobby element as follows,
 


Further you can preview the data points which satisfy the condition by clicking on the ‘Preview’ button.

Hope this helps to build up the lobby!

Best Regards,
Bhagya


 

Userlevel 2
Badge +6

Hi Bhagya,

Thank you for the response.

 

I had considered this, though the projected quantity for a given week takes the last weeks value into consideration, which complicates things a bit more.

 

For example, a part might currently have an available quantity of 200. The next few weeks may look like this.

Due Date Demand Supply Projected
11/10/21 100 0 100
18/10/21 50 25 75
25/10/21 100 0 -25

 

For the first week, the available quantity is used to calculate the projected value, which is workable. But for the following weeks, the projected quantity is calculated using the previous weeks value, which I can’t access due to it being null.

 

Is a custom table perhaps the best way to go? Or do you think this logic can still be repeated in the data source?