0

I'm in the process of writing a SuiteTalk integration, and I've hit an interesting data transformation issue. In the target system, we have a sort of notes table which has a category column and then the notes column. Data going into that table from NetSuite could be several different fields on a single entity in NetSuite terms, but several records of different categories in our terms.

If you take the example of a Sales Order, you might have two text fields that we need to bring across as notes. For each of those fields I need to create a row, with both the notes field in the same column but separate rows. This would allow me to add a dynamic column that give the category for each of those fields.

So instead of

SO number notes 1 notes 2

SO1234567 some text1 some text2

You’d get

SO Number Category Text

SO1234567 category 1 some text1

SO1234567 category 2 some text2

The two problems I’m really trying to solve here are:

  1. Where can I store the category name? It can’t be the field name in NetSuite. It needs to be configurable per customer as the number of notes fields in each record type might vary across implementations. This is currently my main blocker.
  2. Performance – I could create a saved search for each type of note, and bring one row across each time, but that’s not really an acceptable performance hit if I can do it all in one call.

I use Saved Searches in NetSuite to provide a configurable way of filtering the data to import into the target system.

If I were writing a SQL query, i would use the UNION clause, with the first column being a dynamic column denoting the category and the second column being the actual data field from NetSuite. My ideal would be if I could somehow do a similar thing either as a single saved search, or as one saved search per entity, without having to create any additional fields within NetSuite itself, so that from the SuiteTalk side I can just query the search and pull in the data. As a temporary kludge, I now have multiple saved searches in NetSuite, one per category, and within the ID of the saved search I expect the category name and an indicator of the record type. I then have a parent search which gives me the searches for that record type - it's very clunky, and ultimately results in far too many round trips for me to be satisfied.

Any idea if something like this is at all possible?? Or if not, is there a way of solving this without hard-coding the category values in the front end? Even if I can bring back multiple recordsets in one call, that would be a performance enhancement.

I've asked the same question on the NetSuite forums but to no avail.

Thanks

Community
  • 1
  • 1
Emma
  • 19
  • 7

1 Answers1

0

At first read it sounds like you are trying to query a set of fields from entities. The fields may be custom fields or built in fields. Can you not just query the entities where your saved search has all the potential category columns and then transform the received data into categories?

Otherwise please provide more specifics in Netsuite terms about what you are trying to do.

bknights
  • 14,408
  • 2
  • 18
  • 31
  • Oops, didn't realise I hadn't completed my last comment. I've edited my post to provide a bit more detail on what I'm trying to achieve. My main problem is actually how to make the category type configurable - so that I don't have to write new code for every customer. Also the preference would be to avoid too much screen configuration in NetSuite as that's not our forte, which is why we're trying to limit our NetSuite interaction to saved searches. We could potentially add SuiteScript saved searches to solve the problem if we needed to. – Emma Oct 16 '17 at 09:11