Begins with or contains

Hi Community,

Need some help please, in my WF I want to add a column and I want to say IF the Item number "begins with, and I have "22" different ATO models the that it can be, and the order type = Sales Orders then select the line for that item from the list.

I think its a IF contains, else formula but I don't know how to write it.

Attaching the sample data, if someone can help with the logic please.

I and already bringing into my WF the ATO_Model_Line, in a Join / Union was was going to use this in the formula as the column I was going to rewrite the formula in.

But I'm sure someone can correct that "bad Idea" 😀😀

TIA Karl.

Test Data.xlsx

ATO_Model_Line.xlsx

Workflow

Accepted answers

echuong1

A little different way than using an if begins with.

I started by parsing out the item, since the first part of it should correspond with the reference list. I then used the first part as my criteria for a join/vlookup to the reference list to bring in the line value. Because there was fallout (item numbers not in the reference list), I used a union to bring the two data populations back together. I then used a select to remove the parsed out fields, since I didn't need them anymore. I used a formula to update the line value to only keep the value if the order type was sales order.

Hope this helps!

New Workflow1.yxmd

All comments

echuong1

A little different way than using an if begins with.

Hope this helps!

New Workflow1.yxmd

AngelosPachis

Hi @Karl_Spratt ,

May I suggest you use a "Find & Replace" tool instead of an If statement.

What the Find & Replace tool will do is look at the Beginning of the field "Item" and search for the values contained in the column "Item Number". If it finds that value at the beginning of the "ITEM" field, it will append the field "Line"

Screenshot 2021-01-26 205326.jpg

As you can see, some records of the "Line" column will then be null; those are records that do not begin with any of the strings contained in the "Item Number" column. You can then use a filter tool to exclude those nulls and only keep the records that match.