Does anyone know why List/Record fields of type Do...
# suiteanalyticss
scottvonduhn
10/20/2021, 2:30 PMDoes anyone know why List/Record fields of type Document cannot be retrieved in the NetSuite2.com data source? I am trying to convert some old queries from NetSuite.com to NetSSuite2.com, but a few of them cannot be converted because they reference a File/Document column. Was this intentional by NetSuite? Is there any workaround?
d
dynamicl
10/20/2021, 3:23 PMDo you have an example record type you are looking at?
s
scottvonduhn
10/20/2021, 3:39 PMOn transactions, we have a custom field calls PDF File (id: custbody_pdf_file), but the column is not accessible. If I try to add it to my SELECT clause, I get an error:
[NetSuite][SuiteAnalytics Connect JDBC Driver][OpenAccess SDK SQL Engine] Column: transaction.custbody_pdf_file not found. [10125]scottvonduhn
10/20/2021, 3:42 PMThis field IS accessible via the NetSuite.com schema, though, and there are several others on various custom record types that have the same problem. The only thing they have in common is they are all Document fields.
scottvonduhn
10/20/2021, 3:44 PMMy query:
SELECT id, custbody_pdf_file FROM transaction WHERE ROWNUM < 10d
dynamicl
10/20/2021, 3:44 PMtry this and see if its listed
dynamicl
10/20/2021, 3:44 PMSELECT * FROM OPENQUERY([NETSUITE2], 'SELECT * FROM oa_columns WHERE TABLE_NAME = ''transaction''')
s
scottvonduhn
10/20/2021, 3:45 PMIt is not listed
d
dynamicl
10/20/2021, 3:46 PMmmm, could be that data type isnt being exposed then
s
scottvonduhn
10/20/2021, 3:46 PMBut yet, the Connect schema still allows access to that field
d
dynamicl
10/20/2021, 3:47 PMSELECT * FROM OPENQUERY([NETSUITE2], 'SELECT * FROM oa_columns WHERE COLUMN_NAME = ''custbody_pdf_file''')
dynamicl
10/20/2021, 3:47 PMsame issue?
dynamicl
10/20/2021, 3:48 PMJust wondering if there is a mapping table
s
scottvonduhn
10/20/2021, 3:51 PMNope, nothing comes back. So frustrating.
scottvonduhn
10/20/2021, 3:54 PMI suppose I could modify my scripts that sets these custom field to also store the File id in a customer Integer field as well. It's just going to be a lot of work and data duplication for no good reason.
d
dynamicl
10/20/2021, 3:55 PMOut of interest, is the body field type = document ?
s
scottvonduhn
10/20/2021, 3:56 PMyes, Type is Document
d
dynamicl
10/20/2021, 4:03 PMI think the records catalog will confirm too ... Setup > Records Catalog > transaction
s
scottvonduhn
10/20/2021, 4:06 PMIndeed, it is not listed there either. Is this documented somewhere in SuiteAnswers? that Document fields are not accessible via the Analytic API?
d
dynamicl
10/20/2021, 4:08 PMI've just had a look around the help section but not in SA. Seems like they are not exposing that datatype via netsuite2.com . Could be worth a ticket considering its in netsuite.com
s
scottvonduhn
10/20/2021, 4:08 PMI'm not even sure that we needed these to be document fields, either, it's more important that we can query the value. It just made sense to use the correct field type back when they were set up. But if that data type is restricted, it makes it much less useful.
d
dynamicl
10/20/2021, 4:09 PMI'm sure it will go in the 'enhancement' pot
s
scottvonduhn
10/20/2021, 4:09 PMYeah, I think this will end up going to support. Probably a U7 since they will tell me to use a workaround
scottvonduhn
10/20/2021, 4:09 PM🤷
d
dynamicl
10/20/2021, 4:10 PMI'm in the same boat, planning on creating a mapping table with a view to creating some code to change all the scripts
dynamicl
10/20/2021, 4:10 PMEither way, not fun
s
scottvonduhn
10/20/2021, 4:11 PMIt means changing both queries and code for us now, which is double the pain.
d
dynamicl
10/20/2021, 4:12 PMI think a mapping table should work, ill let you know how i get on.
👍 1