How to manage access to columns that are not always existing?

1

Hello,

I try to connect Magento to read order to send into ERP Dynamics Nav.
File structure of Magento's API is dynamically built.
Fields or sections are not always present in JSON.
In EasyMorph I get a table structure and I am trying to find out what kind of data is added on the row.
I use action "Calculate new columns(s)" to add a line type.

Function test if data is present in column to mark line type.
I have 13 differents line types to find and I have only one expression. It works unless the column is not present.

I tried changing the expression to test if the column exists before checking its contents but it doesn't work.

if(if(columnexists("extension_attributes.shipping_assignments{*}.shipping.address.street{*}"),
	not isempty([extension_attributes.shipping_assignments{*}.shipping.address.street{*}]),false()),
		"shipping.address.street",
		"general")

Error: Column [extension_attributes.shipping_assignments{}.shipping.address.street{}] not found Error position 113
Source: action "Calculate new column(s)", module "GetOrderMagento", table "Order"

How to do this calculation all at once without having to do a multiple derivative table?
Can a function retrieve the value of a field with its name as with the function columnexists?

Thanks in advance for your help.

2

Hi Florent and welcome to the Community!

There are two constructs in EasyMorph for conditional calculations: the if() function, and the IF…THEN…ELSE operator.

The difference between them is that both “then” and “else” parts are always evaluated in the if() function, no matter if the condition is true or false. In the IF operator, the “else” part is only evaluated when the condition is false, and the “then” part is only evaluated when the condition is true.

To make sure your expression doesn’t fail because a field is missing, use the IF operator instead of the if() function.

UPDATE
However, even if the IF operator evaluates differently, all references to columns must be valid. Therefore columns should exist in any case.

3

I tried the IF..THEN..ELSE and ASSUME operators and this error message is still present.

Example with IF..THEN..ELSE:
IF columnexists("extension_attributes.shipping_assignments{}.shipping.address.street{}") THEN not isempty([extension_attributes.shipping_assignments{}.shipping.address.street{}]) ELSE false()

Error: Column [extension_attributes.shipping_assignments{}.shipping.address.street{}] not found Error position 109
Source: action "Calculate new column(s)", module "GetOrderMagento", table "Order"

Example with ASSUME:
ASSUME columnexists("extension_attributes.shipping_assignments{}.shipping.address.street{}")
not isempty([extension_attributes.shipping_assignments{}.shipping.address.street{}])

Error: Column [extension_attributes.shipping_assignments{}.shipping.address.street{}] not found Error position 109
Source: action "Calculate new column(s)", module "GetOrderMagento", table "Order"

Columns mapping may be parser before the operators. Which would explain the result.

4

I found a solution.
I create a new table with fixed columns with data empty.

image

After using “Append another table”, all columns are presents and duplicate columns name finish by “(2)” and can be ignored.

image

I found a workaround but I think it is missing either a function that allows to retrieve a value from a column according to its name, or that the IF…ELSE is interpreted before doing the mapping of the columns .

Thanks for the quick help.

5

That doesn't look right to me. I'll talk to our development team to figure out why it's so.

6

OK, it turns out that fields must exist before an expression is evaluated. My initial explanation wasn't correct :lying_face:. I've updated it.

In this case, yes, the solution you've come up with is the simplest.

Note taken.