Fetch XML Builder: IN Operator with a lot of values

(0) Share

Report

Posted on by Joel D

355

Hello,

Within FetchXML Builder, I want to retrieve the records based of a field value specified inside an excel file.
I'll use these records for a bulk update.

Inserting multiple values inside the "Value" while using the IN operator is not immediate, as you have to add a comma to each one and put them on a single line.

But what if I have hundreds of values inside the excel file?

Is there a way to overcome this issue?

These are the steps I am doing:

- I opened Bulk Data Updater and then went to FetchXML Builder to create my query and retrieve the records I wanted to update.

- To create the filter for the name, what I suppose to do is entering the following values in a single line, separated by a comma:

- I have copied these names from an excel file (I am using a sample here, but in a real-life scenario, I may have 100+ records):

- I selected all the names, copied and put them on a single line, and added a comma to separate them manually for each record.

Is there a fastest/easy way to achieve my goal?

All responses (3)

Answers (1)

Verified answer

- Matt Bayes - 890 on at

Like (0)

Report
RE: Fetch XML Builder: IN Operator with a lot of values

Hey Joel,

I've actually had a similar requirement before, and we had something like 1000 records I needed to filter on.

In order to achieve this, we started with an Excel file and used the CONCAT function to build our comma-separated list.

Here's a screenshot of an example:

Based on this screenshot did the following:

Put all of our values we wanted in column A

In column B, row 1, we set this as =A1

In column B, row 2, we used the function =CONCAT(B1, ",", A2)

What this function does is concatenate the value in B1 with the value in A2 and puts a comma in between them. When we drag this formula down the column to the ending row, it will automatically pickup the next values in the list and build it dynamically.

If you do have a lot of rows, this could become very slow as you approach the last row, but in your case I think 100 items wouldn't be so bad.

If you have questions don't hesitate to reach out!

Thanks!

Matt Bayes
Joel D 355 on at

Like (0)

Report

RE: Fetch XML Builder: IN Operator with a lot of values

Hello,

Thank you for your comment.

I have the same issue with using this method, as the tool doesn't let me add multiple values simultaneously.

I need to add multiple values at once, even if they are in separate lines, without commas.
Ossama 3 on at

Like (0)

Report

RE: Fetch XML Builder: IN Operator with a lot of values

Hi,

Could you try to add them in separated node within FetchXML Builder like the below: