Target Lookup Exact Match

  • 95Views
  • Last Post 08 January 2021
0
votes
Scott Parkin posted this 04 January 2021

I have a DB Import task that uses a Target Lookup field to find an ID based on a SKU field for a product.

Our SKU's are constructed in parts and may build on each other for various components:

  • AAA-BBB
  • AAA-BBB-CCC
  • AAA-BBB-CCC-1

Each of the above is a single product and I need to update only the exact SKU.

The import is failing as the lookup for the first example above is finding all 3 records and the lookup must only return a single record.

How can I force an Exact Match lookup?

Order By: Standard | Newest | Votes
0
votes
Mariia Zaharova posted this 04 January 2021

Hello Scott,

 

Thank you for contacting Skyvia Support Team!

 

Skyvia performs an exact lookup, i.e. it uses the EQUALS operator, not the LIKE one. This means, that the issue is most likely related to the fact that you have several records with the same SKU value in your Zoho CRM instance.

You can check this by executing a query like this one (please use the SKU value from the error log):

SELECT t.Id, t."SKU", t."Product Name"

  FROM Products AS t

WHERE (t."SKU" = 'AAA-BBB')

 

Our example (we use the "Product Code" field instead of the "SKU")

 

We are looking forward to your reply.

 

Best regards,

Mariia

0
votes
Scott Parkin posted this 04 January 2021

Thanks for the reply, but I had already checked that and confirmed there are no duplicates. Here is the query per your request on one of the error rows (the error was "The specified lookup is ambiguous. Lookup must return at most one row for the specified lookup condition.":

0
votes
Scott Parkin posted this 04 January 2021

The hyphens are causing the issue. If I change the SQL to this I get a single result:

SELECT t.Id, t."SKU", t."Product Name"

  FROM Products AS t

WHERE REPLACE(t."SKU",'-','') like '4MDBBKITUS'

 

Is there a way I can do this in Skyvia?

0
votes
Mariia Zaharova posted this 08 January 2021

Hello Scott,

 

Thank you for your reply.

 

The hyphens are causing the issue.

 

Unfortunately, we could not reproduce such an issue even with hyphens in the values. Please perform the following steps:

- create a new Export package with the Zoho CRM connection as a source;

- select the Product object as a source;

- select Id, SKU, "Product Name" fields;

- run Export and send us the CSV file.

 

Is there a way I can do this in Skyvia?

 

No, there is no such possibility.

 

We are looking forward to your reply.

 

Best regards,

Mariia

Close