Return an error message when no matches found on a lookup

[Jim_Clarke]

I would like to return an error message when no matches are found on a lookup. I am using the following handlebars expression in my response mapping, but it doesn't seem to work on 0 matches found:
{{#if errors[0].message}}{{errors[0].message}}{{else if data.length}}{{NULL}}{{else}}"Product not found"{{/if}}

[tylerlamparter]

Could you do something like this?

Stop Flow if a Lookup yields zero results?

Hi Steve Klett, If this is the case, then you can set the success path in your import lookup. Ex: let say from the lookup import for success, you got results as: {results [{id: "123"}]. and for empty response you got as {results : []}. Now all you need to do is set the success path as "results[0].id", now always it will check for the path present or not from the response, otherwise, it will give you an error. you can find the success path in the import lookup --> Non-standard API respose …

[Jim_Clarke]

Unless I misunderstand something, that doesn't provide what I need. I want to use the result mapping of a lookup to put an value into an ErrorMessage field if no matches were found, or if any error occurred, and then continue on to the next steps. I have enabled the option to "proceed to the next application...", but I can't get the capture of the error message to work.

[tylerlamparter]

I see. Result mapping doesn't support handlebar expressions, so you wouldn't be able to have that here. Going a different way, why not just response map the errors and also response map the data as separate mappings? Then, in your next step or on a branch right after this step, filter where data.length < 1 OR errors.length > 0?

[tylerlamparter]

The initial suggestion I gave would work for what you're trying to accomplish, but it wouldn't be as clear what the issue is since the error message wouldn't be "Product not found" and instead would be something like "Unable to extract paths from response body using path: _ids, Response:". Ultimately, though, you would get an error when nothing is found and you could handle that downstream however you need.

Now you can get exactly what you're asking for, but it requires a preSavePage script to do it. Here is a preSavePage script to do what you're wanting:

function preSavePage (options) {
  
  let newErrorsAndRetryData = [];
  
  if (options.data.length === 0 && options.errors.length === 0) {
    newErrorsAndRetryData.push({
      errors: [{
        "code": 404,
        "classification": "missing",
        "message": "Product not found"
      }]
  })
  }
  
  return {
    data: options.data,
    errors: options.errors,
    abort: false,
    newErrorsAndRetryData: newErrorsAndRetryData
  }
}

[Jim_Clarke]

Tyler,

Thanks for the detailed responses! I will try the preSavePage script.
Just a few comments:

• I am surprised that response fields don't support handlebars. The preview output window shows partial success with a handlebar expression (see below). How should I know where I can use them and where I can not?
  
  
• It would be great if on a Lookup block you could easily configure it to return an error with 0 matches, and/or with >1 match. In my experience with lookups in integrations, 80+% of the time you are looking for 1 and only 1 match, and a easy way to log 0 or >1 with a customized error message would be great!
• Another thing that would be very useful is a "dummy" connector returns a error when a special field is set, and does nothing otherwise. This seems like it would be very useful in debugging, and would allow custom error messages to be captured based on configurable conditions. I could just support 1 object with 1 field (ErrorMessage), and log an error if ErrorMessage is anything but null/"".

Jim

[tylerlamparter]

I am surprised that response fields don't support handlebars. The preview output window shows partial success with a handlebar expression (see below). How should I know where I can use them and where I can not?

The handlebars working in preview indicate there is a bug where the preview setup does not match actual runtime behavior. We are currently working to rebuild the preview engine to be directly in line with the runtime engine. Historically, these two have been split, which has led to behavior like this. As for where you can use handlebars, if you ever see the {} icon next to a field, then you can for sure use them there. However, there are other places where you can use them, but the {} doesn't show. We're also trying to clean this up so it's very clear where you can and can't use handlebars. As for the future on result mapping, we do want to bring handlebar support here.

It would be great if on a Lookup block you could easily configure it to return an error with 0 matches, and/or with >1 match. In my experience with lookups in integrations, 80+% of the time you are looking for 1 and only 1 match, and a easy way to log 0 or >1 with a customized error message would be great!

I'm curious what your use case here is. Do you want to force an error so that you can add the required record into the system you're performing the lookup against and then retry the error from that point so it goes through to subsequent steps? However, if you're just trying to use this "error" as a way to filter the record for downstream processing, then you can just use a simple resultField is empty or resultField is not empty check.

Another thing that would be very useful is a "dummy" connector returns a error when a special field is set, and does nothing otherwise. This seems like it would be very useful in debugging, and would allow custom error messages to be captured based on configurable conditions. I could just support 1 object with 1 field (ErrorMessage), and log an error if ErrorMessage is anything but null/""

Correct me if I'm wrong here, but are you suggesting a "data validation" tool step? Essentially a step where you add rules where a record has to have X, Y, and Z, and if it doesn't, then fail the record?

[Jim_Clarke]

Tyler,

• Glad to hear you are working on making is more clear, and adding support for handlebars where it is not supported!
• Yes, we want the error to be logged so a user can add the missing record and then retry/resolve the issue.
• Yes, a "data validation" step would also work. Support for most of the pre and post actions (like input filtering) would also be important. Implementing/using a "dummy" connector is less intuitive, but probably quicker for Celigo to add, and it would then support all of the same pre/post actions that import does.

I have one more quick question. I used the preSavePage script, and it works great. I also still have a results mapping entry that puts errors[0].message into a field called SFDCErrorMessage, for later use. However, that block is configured as a "one to many", so my results error message is put at the child level instead of the parent level. How do I results map it to a _Parent level field?

Jim

[tylerlamparter]

It looks like I may be totally wrong here about handlebars being supported in result mapping. I just tried your initial handlebar and changed it to use dot notation, and it's working fine in preview and runtime.

{{#if errors.0.message}}{{errors.0.message}}{{else if data.length}}{{else}}Product not found{{/if}}

This doesn't fix your issue of wanting the error to be thrown on the lookup step, but it would allow you to handle it downstream.

[Jim_Clarke]

Tyler,

Thanks for sticking with this! That's good to know.

I still have the question about mapping the result back to a header level field. The lookup with the result mapping is a detail/line level, and I need to map the error message back into a header level field. Otherwise in the last block I will need to loop through all of the items for each header, and check/get the error message from each (if there). It doesn't matter to me if I just get the 1st item error, the last item error, or all concatenated together.

Jim

[tylerlamparter]

For the data validation, we've discussed having some tool step where you could set the validation of how a record should be and what values a record should have. Is that what you're thinking as well?

As for the mapping to parent, that's not possible without a postResponseMap script. How would you handle cases where there are multiple errors? Would you just concatenate the messages together? If so, then you could use handlebars in mapper 2.0 to do that without needing any scripts. Where you would need a postResponseMap script is if you want to easily branch or filter when "any errorMessage field is populated".

Script:

function postResponseMap(options) {
  return options.postResponseMapData.map((item) => {
    const SFDCErrorMessages = item.lines
      .filter(line => line.SFDCErrorMessage)
      .map(line => line.SFDCErrorMessage)
      .join('');

    return {
      ...item,
      hasSFDCErrorMessage: Boolean(SFDCErrorMessages),
      SFDCErrorMessage: SFDCErrorMessages,
    };
  });
}

Script input sample:

{
  "postResponseMapData": [
    {
      "_id": "6398ddf316be761fb16a6523",
      "lines": [
        {
          "no": "1",
          "SFDCErrorMessage": "error message 1"
        },
        {
          "no": "2",
          "SFDCErrorMessage": "error message 2"
        }
      ]
    }
  ]
}

Script output:

[
  {
    "_id": "6398ddf316be761fb16a6523",
    "lines": [
      {
        "no": "1",
        "SFDCErrorMessage": "error message 1"
      },
      {
        "no": "2",
        "SFDCErrorMessage": "error message 2"
      }
    ],
    "hasSFDCErrorMessage": true,
    "SFDCErrorMessage": "error message 1error message 2"
  }
]

[Jim_Clarke]

Tyler,

That's fantastic! Could you please also provide a quick handlebars sample that would concatenate them together?

Jim

[tylerlamparter]

Something like this:

{{#if lines}}{{#each lines}}{{#if SFDCErrorMessage}}Product {{no}}: {{SFDCErrorMessage}}; {{/if}}{{/each}}{{else}}No errors{{/if}}

[Jim_Clarke]

Perfect! Thanks again!

And in response to your earlier question about a data validation, as long as you can return log a custom error message, that sounds good!

Jim