Calculated Fields – A Checklist

I needed away to roll up entries from a custom table to display as a single value under an item record. What a meticulous and tedious pain this was. I can’t think of any more applicable adjectives, or I’d use them. I needed a checklist!

To be fair, NetSuite is great about letting you create a saved search to display summary results in what they call a “Calculated Custom Field”. It displays as if the value was actually stored with the entity, but it’s not, it’s built on the fly. In my case, I needed to show the SuiteCommerce Advanced base image from the list of images associated with any item.

The task at hand presented multiple problems. I’ll spare you the details of the first problem, which was an inability to write a saved search listing only images associated with a single item. I learned this is not supported in NetSuite, so I had to built my own custom table. Note: I used a SuiteQL query against the ItemImage table (Thank you Tim Dietrich) along with a Map/Reduce script. This let me build a copy of the ItemImage table which WAS accessible from a Saved Search.

This brings me to the list of all quirky things required show the one file name under an item. Here is that list!

  • Identify the standard or custom table you wish to pull your value from. In my case it was a custom table called “Item Images”.
  • Create a saved search against your source table.
    • Make your search public.
    • Add any criteria you like. In my case it was “Base Image” = true.
    • Include the entity type as the first field in your results.
    • Make sure the results are “Summary Results”. In my case I used MINIMUM File Name
    • Create an Available filter that limits results to a single entity and make sure to check “Show in Filter Region”.
Add any criteria you like
The first field in results should be the entity, or in my case, Item. Make sure the results are “Summary Results”. I don’t believe you can select “Group” under “Summary Type”. I chose “Minimum” and it works!
Set an available filter that matches your entity type. Again, in my case, it was Item. Be sure to check “Show in Filter Region.”
  • Create a new Custom Field.
    • Pick the field type that is correct for the rolled up value you will display from your saved search.
    • Uncheck “Store Value”. This value will not be stored, but calculated on the fly.
    • Under “Validation and Defaulting”, select the saved search your just created.
Select the proper field type. Uncheck “Store Value” and associate your calculated field with your new saved search.
Be sure to include the parent record reference in your custom table.

Tying up lose ends, I’ll show the definition of my custom table.

Be sure your entity field is also set to “Record is Parent”.

Hopefully, in the future, if we all follow this checklist, we’ll be thinking happy thoughts about NetSuite and not letting fly any uncomplimentary adjectives. Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s