Forum Stats

  • 3,854,352 Users
  • 2,264,358 Discussions
  • 7,905,663 Comments

Discussions

PBCS - Data Management - Mapping Question

2»

Answers

  • elwayisgod
    elwayisgod Member Posts: 103 Green Ribbon
    edited Nov 12, 2018 9:59AM

    Hi John,

    So I have been unsuccessful in getting ANY SQL to actually do anything to any field I try it on.  I was trying to change a value in a 'source' column with anything just to get it to work.  So in this example I want to change '1210 Inventory : Inventory In Stock' to 'New_Account'.  Really just change it to anything.

    DM_FieldName.png

    So I follow the instructions you provided on how to add a map and use SQL.  I'm using this SQL:

    DM_NewSQL.png

    However results don't show anything changing:

    DM_Map_Results.png

    I get an error in the log:

    DM_SQL_Error.png

    So I think something wrong somewhere.  The idea is if I can change any field to anything then atleast I have a working method/template to go off of.  My source is NetSuite, so maybe I need the actual table name and field name and my SQL needs to reference that instead of the column name that is displayed in DM?   I'm not sure I can find out that information or not or if it's available anywhere.

    Anyways, thanks for any suggestions, this is driving me crazy.  Would love to use this feature as it seems really powerful.

  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Nov 12, 2018 10:33AM

    First of all if you are checking explicitly then it should be an equals sign, for example

    WHEN ACCOUNT='ACC1' THEN 'ACC2'

    IS is for functions like IS NULL

  • elwayisgod
    elwayisgod Member Posts: 103 Green Ribbon
    edited Nov 12, 2018 12:57PM

    Thanks John!!!

    Great progress as the '=' will work on explicit maps 

    DM_Fixed.png

    So original issue was I have blank fields coming in and I want to map those to 'No XXXX' or whatever.   So I'm experimenting on the 'Version' field as it's coming in blank thus was trying to get it to change via SQL.

    Version_SQL.png

    But results don't map and I get this error:

    Version_SQL_Error.png

  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Nov 12, 2018 1:03PM

    The database column will not be called Version, look at your target application in Data Management and see which "Data Table Column Name" version is assigned to, it will be a UD column

  • elwayisgod
    elwayisgod Member Posts: 103 Green Ribbon
    edited Nov 12, 2018 2:09PM

    I have 'Type' field mapped to Version.

    Target Appplication:

    Target_Application.png

    Import Format:

    Type_Field.png

  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Nov 12, 2018 2:23PM

    That looks like your source NetSuite app, I mean check the Version data column for your PBCS application which is your target.

    elwayisgod
  • elwayisgod
    elwayisgod Member Posts: 103 Green Ribbon
    edited Nov 12, 2018 2:34PM

    So I changed my Target application and hardcoded in UD1 for Data Table Column Name.  I guess I'm confused as to what this field is for and if I should always have something defined here for the fields I'm interested in.  I'm not FDMEE expert by any means, but I do understand data integration.  And what Sequence is used for.

    New_Target_App.png

    My new SQL that works perfectly is:

    New_DM_SQL.png

    New results are correct as 'Bill' gets changed to NULL and others that aren't = 'Bill' stay as is..

    New_Results.png

    So next step is modifying the source NetSuite 'saved search' to include Product and Department fields.   Once that is done I can then test changing NULL values for those two fields.  We have to modify this saved search as we can't get any other saved search to return results for some reason.  Only this one actually gives us rows of data in DM.

    All this help is much appreciated.  So confusing

    Stay tuned.

  • elwayisgod
    elwayisgod Member Posts: 103 Green Ribbon
    edited Nov 13, 2018 9:10AM

    OK.  So assign a UD value to any dimensions that are not the 'required' dims.  Then that is technically the field name to use in the SQL.  At least that's how I got it to work.  Still fuzzy on 'Sequence' as I left those blank.

    Whoohooo.  Finally.

    The NULL values mapping to 'No Dept NS' for Entity dim and 'No Product NS' for Product dim .

    New_Results2.png