Hey, I'm trying to understand which "amount" colum...
# generalt
Tom Kennedy
10/20/2020, 4:59 PMHey, I'm trying to understand which "amount" column to pull from the transaction lines table. There is Amount, Foreign Amount, Gross Amount, Net Amount. I have several currencies running through so need to identify what is the local currency. Thanks!!
m
Marc Reicher
10/20/2020, 5:00 PMWhat do you mean by
local currencyMarc Reicher
10/20/2020, 5:00 PMMy guess is that you want the Foreign Amount
Marc Reicher
10/20/2020, 5:01 PMForeign Amount = currency that the transaction actually took place in (I assume this is what you want)
Amount = currency of the subsidiary that the transaction took place in
t
Tom Kennedy
10/20/2020, 6:13 PMThanks that helps. I was looking for the Subsidiary reporting currency which is in several currencies, then need to convert and consolidate. What are the other amount columns (ie Net amount, Gross amount)?
m
Marc Reicher
10/20/2020, 6:32 PMyeah if you want to consolidate, you've got a bigger task in your future
t
Tom Kennedy
10/20/2020, 6:33 PMAny suggestions, I am working through the consolidated exchange rates tables to match how to apply each rate to the transaction
m
Marc Reicher
10/20/2020, 6:34 PMyeah that's the correct table
Marc Reicher
10/20/2020, 6:34 PMwhat are you really trying to do though
t
Tom Kennedy
10/20/2020, 6:35 PMBreak out each sub at their functional currency, then show a view of them at a consolidated level
m
Marc Reicher
10/20/2020, 6:47 PMlike the trial balance for each sub? or just a list of all transactions?
t
Tom Kennedy
10/20/2020, 7:38 PMBoth, But Trial balance is the focus right now
m
Marc Reicher
10/20/2020, 8:38 PMSo, I've spent quite a bit of time on this before. It is doable, but a bit of a rabbit hole. Mind if I ask what your use case is?
Marc Reicher
10/20/2020, 8:38 PMJust building a custom report for your accounting team?
t
Tom Kennedy
10/20/2020, 9:39 PMNo, remaking the reports in PowerBI, so need to make a data model from the raw data
Tom Kennedy
10/22/2020, 6:27 PMDo you have any experience with taking the raw netsuite tables and creating a data model? @Marc Reicher
Tom Kennedy
10/26/2020, 3:50 PMHey @Marc Reicher, I have made some good progress reconciling the data from building up from scratch. But identified one gap from the direct link into the netsuite tables compared to the netsuite interface. In the transaction lines table, the only transaction types I have a journal entries and Purchase orders(without dates). However I am missing Vendor bills which is making up large expense amounts. I am looking for the VendorBill table which seems to be a fact table with that data, but that is not available right now, I'm working with the team to check it out. Have you had this similar experience? Not sure where that data could be, any idea of other table names I should be looking for? My initial understanding was the all the data sat in the transaction lines table, this may be incorrect. Let me know if you have any advice.
m
Marc Reicher
10/26/2020, 4:42 PMWhat role are you using?
t
Tom Kennedy
10/26/2020, 11:48 PMHmm not sure, I am going to find out. Do you think this is a permissions issue?
m
Marc Reicher
10/26/2020, 11:50 PMYeah you need to make sure your role has permissions to view all transactions. Unfortunately, this is not an easy task the way netsuite's roles/permissions work. Your best bet is to create a token associated with an admin role if you just want to get something going and are willing to do that from a security standpoint