1

This seems like it should be simple, but for some reason I can't get this to work... I am trying to build a transaction saved search that shows the user (person viewing the search) all transfer orders that have their location in either the {location} field or {transferlocation} field. Here is the formula (Numeric) I am adding to the criteria and setting is equal to 1:

CASE WHEN {location} = {user.location} THEN 1 ELSE 0 END

Seems pretty simple, but this formula errors out. I have also tried this and it doesn't error, but I get 0 results:

CASE WHEN {location} LIKE {user.location} THEN 1 ELSE 0 END

Once I get this to succeed I should be able to either add to this formula or add a new formula criteria for the {transferlocation}. Either way, any suggestions would be great. Below I will add all criteria/results/filters just for transparency:

Criteria:

Main Line is True

Type is Transfer Order

Status is any of Transfer Order:Partially Fulfilled, Transfer Order:Pending Approval, Transfer Order:Pending Fulfillment, Transfer Order:Pending Receipt, Transfer Order:Pending Receipt/Partially Fulfilled

(not working) Formula (Numeric) is 1 CASE WHEN {user.location} = {location} OR {user.location} = {transferlocation} THEN 1 ELSE 0 END

Results:

Date Created

Document Number

Location

To Location

Filters:

Date Created

Cody Schram
  • 19
  • 6

2 Answers2

2

In a lot of case, when you use Numeric Formula, it seems like NS prefer using internalIds, so use this in your formula and it should work (tested):

CASE WHEN {location.id} = {user.location.id} THEN 1 ELSE 0 END
B. Assem
  • 1,058
  • 9
  • 12
  • I would give this to both answers if I could, comparing the internal ID worked, this was the formula I ended up going with: ` CASE WHEN {location.id} = {user.location.id} OR {transferlocation.id} = {user.location.id} THEN 1 ELSE 0 END ` – Cody Schram Jan 24 '22 at 15:28
1

Most likely due to NetSuite perceiving the possibility of data type differences. Try wrapping your location fields in NVL and/or TO_CHAR or using ...location.id}

Nathan Sutherland
  • 1,191
  • 7
  • 9
  • I would give this to both answers if I could, comparing the internal ID worked, this was the formula I ended up going with: ` CASE WHEN {location.id} = {user.location.id} OR {transferlocation.id} = {user.location.id} THEN 1 ELSE 0 END ` – Cody Schram Jan 24 '22 at 15:27