Saved Search question. Is it possible to create a ...
# suiteanalyticsm
Michael McNamara
10/18/2023, 8:51 PMSaved Search question. Is it possible to create a dynamic field variable? Example: I need this:
{custbody_bookinggoalrecord.custrecord_wk1}
I need to build this based on a stored field number on the transaction which is “1” in the example. Can i do something like
{CONCAT(‘custbody_bookinggoalrecord.custrecord_wk’,{custbody_ace_trans_week})} and have this used as a dynamic field?
watching following 1
m
Marc
10/18/2023, 9:12 PMFor me dynamic field referencing, like the one you described using a concatenation function, isn’t supported.
With case statement you can
m
Michael McNamara
10/18/2023, 9:13 PMI thought of that, but I’m trying to avoid using case for each week in 52 weeks
n
Netsuite Tragic
10/24/2023, 8:27 PMI wonder if you can first build your files and in a separate Custom Text Formula Field called {custody_currentformula} with formula
{custbody_bookinggoalrecord.custrecord_wk’||{custbody_ace_trans_week}
Then use {custody_currentformula} in your search.
A scripted search approach would be able to build your search filter and columns.
Netsuite Tragic
10/24/2023, 8:29 PMYou could pour your lengthy Case or Decode into a custom field then you only have to build it once. But I think the 52 different values are going to be pushing the length limit
m
Michael McNamara
10/24/2023, 9:07 PM@Netsuite Tragic I ended up adding a custom field to the SO that would hold the amount. there was already a field that adds the week # (1,2,3 etc). Then I created a script so it looks up the booking record on the SO with a concat variable (‘custbody_bookinggoalrecord.custrecord_wk’ + recWeek). It picks it up and adds to the SO on save. I also did a mass update script to do this on existing SOs. I figured it was better to have the week amount hardcoded in on the SO for searches and I can use a DISTINCT to get the expected quoata $ goal for that week to compare to.
Michael McNamara
10/24/2023, 9:08 PMvar bookingField = ‘custrecord_wk’ + recWeek;
var fieldLookUp = search.lookupFields({
type: ‘customrecord_bookinggoals’,
id: quotaNum,
columns: [bookingField]
});
var myBookingField = fieldLookUp[bookingField];
soQuotaRec.setValue({
fieldId: ‘custbody_booking_wk_goal’,
value: myBookingField
});
3 Views