Using flattened JSON field as a filter

Ask a question
1

I have a scheduled flow that reads customers from System A, creates them in System B (where a customer ID is assigned), and then uses the enrich step to write system B’s customer ID back to a field in System A.

This flow is filtered to only run for customers that don’t yet have a customer ID in System A, so that a new customer is only created in system B once. It works well when there actually are new customers, however when there are no new customers I get failed run notifications because many of the fields that exist in the flow are from JSON fields that have been flattened. The fields that I’m pulling from the flattened JSON don’t exist when there is nothing to flatten.

Examples below. How can I avoid a “failed” notification driven by using flattened JSON info in my flow?

Primary Billing
The column Addresses.PrimaryBilling no longer exists.

Sub QB Terms ID
The column PaymentTerms.Name no longer exists.
Could not find left column matching “PaymentTerms.Name”

2

Hi @Annika_Cederblad,

It sounds like you’ve built a great flow here. To avoid a “failed” notification stemming from using the step Expand JSON (which causes a flow to error when a data type temporally doesn’t exist), you’d use:

  • Insert static text columns and select its advanced settings “do not create a column if column name already exists”

This step enables your flow to add a column of that data type situationally, so the flow fills in the missing data during the times it isn’t pulled through. For example, after inserting Insert static text columns, the flow will run successfully despite whether or not there were new customers that week (or whichever timeframe happened between your flow runs).

I’d suggest adding this step after Expand JSON and before any subsequent steps that build off of and incorporate the column with temporal, situational data (i.e. your new customer column).


Let me know how that goes for you or if you’d like further clarification. :slight_smile: