SELECT's filter-column name conflicts with the column name in START expression loop

[devmasadajaya]

Hi there,

I have a table recording items in an inventory adjustment, the table is called "Inventory Adjustment Items", whose columns are:

1. Adjustment Id
2. Inventory Item Id
3. Quantity

I also have a table called "Inventory Adjustments" recording the inventory adjustment itself, which has columns:

1. Adjustment Id (as a key and reference to the Inventory Adjustment Items table)
2. Department
3. Section

Finally, I also have a table, called "Inventory Stock Balances", recording the latest inventory quantity snapshot for a Department, Section, and Inventory Item Id combination.

I try to create a webhook API action as follows:

<<START: [Related Adjustment Items]>>
{
  "Department":"<<[_thisrow].[Department]>>",
  "Section":"<<[_thisrow].[Section]>>",
  "Inventory Item Id": "<<[Inventory Item Id]>>",
  "Latest quantity": "<<
ANY(SELECT(Inventory Stock Balances[Latest quantity],
  AND([Department]=[_thisrow].[Department],
            [Section]=[_thisrow].[Section],
            [Inventory Item Id]=[Inventory Item Id]
), TRUE)) + [Quantity]>>",

}

<<END>>

The highlighted Inventory Item Id filter above (bold and italics) does not work. I think the expression is confused by the exact same name of the column filter and the column reference in the "Related Adjustment Items" loop.

Is there any way to fix this confusion? Basically, I want to increment the Inventory Stock Balance record matching the Department-Section-Inventory Item Id key with the quantity input in the adjustment item record.

Latest quantity = select latest quantity with the key + Quantity input.

Thanks in advance for your help,

-- Hendi

[Steve]

Back-references within nested queries ([_THISROW-n])

[devmasadajaya]

Thanks for your response, Steve.

I tried:

1. _thisrow -> It references table "Inventory Adjustments", not the "Inventory Adjustment Items" table
2. _thisrow-1 -> also references table "Inventory Adjustments"
3. _thisrow-2 -> it doesn't recognize "_thisrow-2" by saying "Unable to find column '_thisrow-2'"

This webhook API is a task:

1. to be executed whenever there is an update to "Inventory Adjustments" table
2. to perform an edit, through the API webhook, on table "Inventory Stock Balances" table

In the webhook, I am looping through all the related Inventory Adjustment Items record in the Inventory Adjustment, to perform an edit on the corresponding Inventory Stock Balance record (the relation is through the Department+Section+Inventory Item Id key combination).

Here is the simplified snippet of the webhook body:

{
"Action": "Edit",
"Properties": {
"Locale": "id-ID",
},
"Rows": [

<<START: [Related Adjustment Items]>>
{
  "Department":"<<[_thisrow].[Department]>>",
  "Section":"<<[_thisrow].[Section]>>",
  "Inventory Item Id": "<<[Inventory Item Id]>>",
  "Latest quantity": "<<
ANY(SELECT(Inventory Stock Balances[Latest quantity],
  AND([Department]=[_thisrow].[Department],
            [Section]=[_thisrow].[Section],
            [Inventory Item Id]=[Inventory Item Id]
), TRUE)) + [Quantity]>>",

}

<<END>>

]}

It seems I am still missing a certain way of referencing properly.

Thanks again for your help,

-- Hendi

[Suvrutt_Gurjar · Accepted solution]

---

@devmasadajaya wrote:

I think the expression is confused by the exact same name of the column filter and the column reference in the "Related Adjustment Items" loop.

Is there any way to fix this confusion?

---

Maybe I have not understood the issue precisely. However if the error is just because of identical name, is it possible that you can slightly change the names of the column in respective tables and test once?

Something like

[Inventory_Item_Id_IA]  for the column name in Inventory Adjustments table and 

[Inventory_item_Id_ISB] for the column name in Inventory Stock Balance table?

[devmasadajaya]

Yes, Suvrutt, thanks for your suggestion. Changing the name is what I have done yesterday, and it works just fine.

I am just curious in how we can handle the column name reference ambiguity. 🙂

[Suvrutt_Gurjar]

---

@devmasadajaya wrote:

Yes, Suvrutt, thanks for your suggestion. Changing the name is what I have done yesterday, and it works just fine.

---

You are welcome. Thank you for the update. Nice to know it works.

---

@devmasadajaya wrote:

I am just curious in how we can handle the column name reference ambiguity.

---

Could you elaborate.