How do we set conditional logic to allocate inventory to specific fields within Netsuite based on a CSV line location field

Hi all,

So I have a question that hopefully someone can assist with.

We have a CSV that needs to be imported into location-based Netsuite custom fields.

This all works file when we're pushing on a one-field-to-one-location basis (i.e. where the file has only one location and one group of quantities), though how would we go about setting up conditional logic to specify inventory to specific fields within Netsuite based on a CSV line location field.

We basically need to read the location first, then allocate stock levels to location-specific fields. 

What we're trying to achieve is having an information list of inventory levels within Netsuite for a range of three different locations.

Below is what we've mapped so far in Celigo, though we need to first read the "inventory_location" from each line of the CSV and set each of the "qty_*" values to their respectively named fields within Netsuite.

i.e. if the "inventory_location" = MEL2 and the SKU = ABC123 we need to insert that product's respective "qty_*" values into the vic_ field names in Netsuite, if the "inventory_location" = "WA2" then we need to insert the values into "wa_" field names.

I'm sure this is a fairly simple process.

The other thing we're going to need to do is do a lookup of a field (our 3PL provider uses different names for locations than our internal names within Netsuite). e.g. the 3PL's location is "MEL2", whilst in our Netsuite system it is: "VIC Warehouse".

Hopefully it's not too difficult if someone can provide some pointers or point us in the right direction to an article that may explain how we can do this from CSV.

Many thanks,

 

Christian

0

Comments

10 comments
Date Votes
  • AFAIK this is only possible with scripting currently. What I would normally do is denormalize the data so its no longer an array but a list of objects that is easy to map, like so:

    {
    "VIC": {
    "qty_a": 1
    "qty_b": 1
     },
    "QLD": {
    "qty_a": 1
    "qty_b": 1
     }
    }

    With the above data format you can easily map the fields as VIC.qty_a and QLD.qty_b into separate fields in NetSuite.

    The script would be something like this:

    newData = {}
    data.forEach(elem => newData[elem.location] = elem)
    return newData
    0
  • Christian Bannard We recently released a feature transformation 2.0 for all exports. You could also try conditional logic in the handlebars expression there. An example would look like the image below. You could create conditions as needed.

    With this approach,

    1. you could transform your export data to what you want in NetSuite using Transformation 2.0.
    2. Eventually, in the import mapping, it will be a 1:1 mapping.


    0
  • Hi Amanjot Singh, thanks for that, looks like the best path forward as we'd ideally like to avoid custom scripting where possible from a longevity and reduced administration perspective.

    Will start seeing if we can implement it in a similar fashion to that which you've proposed and report back on how it goes.

    Many thanks,

    Christian

    0
  • The option to create an output record from an input record is missing when transforming from CSV it seems?

    Am only able to "Create output record{} from input rows[]" per the following screenshot?

    This is what it results in:

    As you will see on my screenshot above, what I'm trying to filter by and then display location-specific quantities for, in their own respective fields, is the "inventory_location" field.

    0
  • Maybe a bug, or someone has fixed a few lines of code at the back-end, but after saving the entire record having written it all out as "rows", I closed out the transformation screen after testing and getting it all functional, load a new sample CSV to test with other inventory_locations and when I went back into the transform screen, when previewing, the screen had suddenly changed to display the option you originally displayed on your screenshot (record to record, rather than row to row) and all of my queries were suddenly invalid, saying that I needed to use a record.xyz rather than a row.xyz (see my original screenshot to you earlier where it had row to row on the dropdowns and how I'd structured the start of my compare to the subsequent screen below that I ended up having to change it to when it all suddenly changed.

     

    0
  • I think I've also found a bug in the transformer, where results are disappearing even though they don't for other fields with exactly the same settings, as follows:

    The only way I discovered this was during the final mapping in the import report where that QLDQtyOnHand didn't exist in the field list at all. I deleted it and recreated it as a new line numerous times, copied and pasted the VICQtyOnHand compare which works fine, into the QLD one and as soon as the "inventory_location" was changed it disappeared, so looks like there's a bug somewhere.

    0
  • Another observation, whether correct or not...

    They all use the same compare with different fields and as you can see they appear in the Output window, and their fields also appear in the field list when mapping the final import.

    0
  • As an aside the file is a CSV, so aside from commas, it's a flat text file with no mark-up of any kind, so something is processing it incorrectly (the file only contains a list of similar records to the visible information in the "Input" section on the above screenshot.

    0
  • Christian Bannard couple questions:

    1. Did you have the group records by fields selected previously? That's how you end up with "rows" instead of "records".
    2. As for the field not showing up if nothing is there, do you have this setting on the field level set to return null? Default behavior to to not have the field at all if nothing is returned so you need to set a new default.
    0
  • Hi Tyler Lamparter

    I think we're all good now.

    You're correct, I originally had the rows grouped and had removed the grouping, so thankyou for solving that little mystery!

    Per your question 2, that behaviour of not displaying anything where the field value is empty was exactly what we were after, so your recommendation was spot on!

    I modified the mapping in the transform to "Use null as default value" and the record displayed as a null (which is fine), after which I set the "output data type" to "Number" to remove extra \n line feeds from the CSV.

    I ended up also needing to do a calculation on the fly as we need to calculate a Quantity Available by subtracting the Qty Committed from the Qty On Hand, which worked a treat:

    {{#compare record.inventory_location '==' 'MEL'}} 
    {{ subtract record.qty_on_hand record.qty_committed }}
    {{/compare}}

    Then finally in the mappings for the Import into Netsuite itself (post-transform) we needed to set Only perform mapping when: to Source record has a value in the Advanced section.

    Finally, the only other thing we needed to do was a lookup of the SKU from Netsuite as part of the initial import given we're only updating existing SKUs, as follows:

    So looks like we're golden and the import is functioning!

    Thankyou for your assistance and we can move on to the next piece of the puzzle!

    Regards,

    Christian

    0

Please sign in to leave a comment.

 

Didn't find what you were looking for?

New post