Formula Help for Primary Field


#1

Hello! So I have a table that I am trying to run a formula to the primary field.
Here is what I have:
IF((Retailer = “Rachel”),("#" &{Order #}) & “-” & “R”) & IF((Retailer = “Mandee”),("#" &{Order #}) & “-” & “M”) & IF((Retailer = “Nichole”),("#" &{Order #}) & “-” & “N”) & IF((Client = “Transfer”),“T”)

Everything is working great, except for the last part “Client = Transfer”
What I’m trying to do is pull the retailer initial and add it to the order number, BUT if the customer shows (Transfer)Name then it will also pull a T

IE: #1231234-MT (or some variation)

Capture


#2

Looks like you want to search if Client contains “Transfer”, so you can do this with FIND.
IF(FIND(“Transfer”,Client)>0,“T”,"")
Also note, this method is case-sensitive


#3

ahhhhh Thank you so much! Got it! Any chance you have the magical answers for this next stump?
I am trying to break my orders down by size quantity ordered. (I am using the sample Product Catalog & Orders - Schedule)

  1. From the Order Lines Item Table I have a single select drop down to select the size ordered.
  2. This rolls up to my Client Orders Table AND my Styles Table
  3. I’m trying to pull this to my styles table (“furniture” in the example) with each size in it’s own column.

Here is what I have:
IF(FIND(“XXS”,{Orders})=0,""," " &((LEN({Orders})-LEN(SUBSTITUTE({Orders},“XXS”,"")))/2))

Here is what happens: I’m not getting an accurate count. I’m assuming it has something to do with the “/2”. I got this formula off of another forum question here and I don’t really know what the 2 means to know how to change it to work!


#4

It looks like it’s checking if no XXS found, then it returns blank “”
If it finds XXS, it compares the length of the string to the length of the string without the XXS’s
Might need to play w/ it, but most likely try /3 if XXS, /2 if XL, etc
update: will want search to include space and comma, otherwise will not be able to distinguish XL from XXL. then divide by /5 if " XXL," or /4 if " XL,"