I would like to store in a custom multi-select ite...
# generall
Luis
04/23/2023, 1:34 AMI would like to store in a custom multi-select item field the customers that have bought the item at least once. I am planning to have a M/R script that will pick up the value from a saved search showing a list of items that were bought by a customer the first time (daily). Is there a better or easier way to achieve this?
I could get the same info via saved search but I actually need this for another saved search where I encountered this join limitation, so I am trying to push this data closer to the item record.
m
MGBC
04/23/2023, 1:36 AMDoes it need to be a multi select or can it just be a text field with all customer names concatenated together
l
Luis
04/23/2023, 1:43 AMHere’s the main saved search I am working on: list of items that a customer has bought in the past with their corresponding current customer-specific price from the customer record or the base price from the item record, if no specific price. I tried the transaction, pricing, customer and item saved search types but I could not get all the same info in one saved search.
This is why I thought I’ll just store the list of customers that bought the item in the item record itself. So I would not need the join anymore. I think having a concatenated text field may work because I can just filter by “contains….” but I am worried about the no. of character limitation of the field.
m
MGBC
04/23/2023, 1:45 AMIf you make it a text area or long text field it has 100,000 characters
l
Luis
04/23/2023, 1:48 AMGot it. Thank you. And I still need to script that? Or can I use a non-stored summary saved search custom field with a scheduled WF to store the value in another custom field? Any reason why you suggested text area/long text vs. multi-select? Is it easier?
Luis
04/23/2023, 1:50 AMOh I just realized something. If I have a customer with names of “ABC” and “AB”, if I filter by “AB”, wouldn’t that incorrectly capture the “ABC” as well because it contains the string AB?
m
MGBC
04/23/2023, 1:58 AMI know you can set a list/record field with a WF by putting the internalid of the list result in a formula
I’ve never tried it but maybe try putting a few internal ids comma separated in a formula in a WF and see if that sets a multi select field
MGBC
04/23/2023, 2:01 AMThe trick would be to make a saved search that would source a non stored value long text field
But use NS_CONCAT({entity}) with summary of Max
That will source all the customers as a long comma separated list into your long text field
MGBC
04/23/2023, 2:02 AMMaybe if the internal id trick works you can use NS CONCAT to find all the internalid s and set your multiple select b
l
Luis
04/23/2023, 3:06 AMI just tested it. It seems that formula cannot be used to set multi-select fields. It’s forcing me to manually select from the list of the internal IDs of the customer.