https://netsuiteprofessionals.com logo
Join Slack
Powered by
What's the best way to show a "calculated field" o...
# general
c
What's the best way to show a "calculated field" on a transaction, such as a field that is display only (not persisted to database) and requires a little bit of SuiteScript or SQL to load the display value? Should I create a read-only text field and alter the display value in a deployed SuiteScript, or is there a better way to build a read-only field with a formula or something?
m
Both are valid options. SQL can be more limited than SuiteScript. Personally I would use SQL if possible and I didn't already have a script. An advantage with SQL/formula approach is that these fields are now accessible via searches as well
c
That would be great.
So do I just create a new custom transaction body field that's not persisted, and set the SQL query in the default value field?
m
Exactly
c
That's great!
How do I write SQL in that field? I mean, what's the syntax to evaluate SQL there?
b
c
Beauty
Thank you both.
Might either of you know how I can get salesOrder.shipMethod via SQL? That property is from a list and isn't available on the transaction itself, i.e. 
SELECT shipmethod FROM Transaction WHERE id = 123
I assume I need to join with a list reference table... I'm a bit new to NetSuite still.
b
at best, you have fragments of sql
c
Hmm?
b
from the query 
SELECT shipmethod FROM Transaction WHERE id = 123
a formula would only consist of 
shipmethod
c
That snippet above is just to show my intention. I'm pulling in other fields, joining with another transaction, etc.
b
specifically 
{shipmethod}
if you want the exact formula
c
I can't seem to find salesOrder.shipMethod in SQL. It's not available in the Transaction table.
b
where are you looking?
c
Transaction
b
too ambiguous
use a link
c
Given an SO with id 123, 
SELECT * FROM Transaction WHERE id = 123
I would think would return all properties of the sales order, but list references appear to list in a through table I assume?
Which would make sense since they can be 1:n
Use a link eh?
b
where are you getting your columns from
netsuite has multiple data sources
fair chance that whatever you are using is incorrect for formulas
formulas arent sql
they are framgents of sql, specifically the columns part of a select statement
c
Oh. Interesting.
Well I’ll be doing 2 joins, so I suppose fragments won’t work.
And as far as data sources, I’ll need to read up.
b
start from Search, which will most closely match what you can do in a formula
👍 1
Setting the Formula Field. which was from the advanced features topic from earlier, gives more details about how the formula works
c
This was much simpler than I expected. I can easily refer to nested reference fields and display the given value:
Copy code
{createdfrom.custbody_sales_order.shipmethod}
Can array of items be displayed in a similar fashion? Say I wanted to display a list of all Service Item Numbers from a related order. I'm trying things like 
{sales_order.items}
, 
{sales_order.item}
, etc., but these all error out as field not found. I'm trying to use the docs to help here but it's not clear how to move forward. Should I be using a saved search and referencing that?
b
Once more from the Advanced Features for Custom Fields help topic: Creating Custom Fields with Values Derived from Summary Search Results
I tend to find the summary search field less useful due to its limitations
c
I'm actually on this page right now.
Yeah, I only seem to be able to do things like SUM, AVG, etc., but can't display a list of strings.
b
use listagg in a formula
c
ohhhh
I'm very close, but I can't edit the formula for the saved search result column, nor can I edit the formula for the custom field that has the saved search picked.
b
did you choose a formula column on your saved search?
c
Ah, no, I had selected the column I wanted to display and was trying to alter the formula cell. I see it now, I've selected Formula (text) as the column and can apply a formula now.
Wonderful. Thanks for hanging in there with me.
This gives me a lot to work with.
b
i will once again emphasize you read the fine print about the limitations of summary columns
c
Great, I'm trying to read everything I can in detail.
If limitation are an issue, would I use SuiteScript in a beforeLoad hook to insert custom fields / data to the display layer?
b
although i am telling you they are an option, i will also tell you that I consider my time spent learning them wasted
c
Well that's good to know.
time spent wasted – on saved search fields / summary columns, or SuiteScript to programatically insert custom fields?
b
that search field on the custom field
c
Okay, I'm having a much better dev experience using beforeLoad SuiteScripts.
13 Views
Open in Slack
PreviousNext
Powered by