@Mikko De La Fuente-Oracle Maybe I am not explaining myself well. I try differently. I have some transaction, let's say Credit Memo with id 4618 When I am looking at the Netsuite web app I see the field "Created From". It has a value of a certain Invoice (4608). This is shown at a transaction level, not at the line level.…

Thanks a lot, @Patrick Fresnosa-Oracle ! The restrictions make perfect sense! If you please can take a look at my other question, I would be very grateful! https://community.oracle.com/netsuite/discussion/comment/16791686 I've spent more than 8 hours trying to figure it out but still no success. Thank you!

@Mikko De La Fuente-Oracle Sorry Mikko, I already got that far, but the "source"="tranid" join is not helpful. What I need is to join between Revenue Element to the Sales Order LINE(!). If I first use the "source"="tranid" join, I loose the line relationship, since there can be several Revenue Elements in a single Sales…

@Mikko De La Fuente-Oracle Maybe you haven't noticed, but I stated I work Netsuite2.com ODBC schema - there is no such table: Transaction_Link . Maybe you meant Previous/NextTransactionLink tables? I've looked into those and I find several previousdocs there. Also, I doubt that Created From comes from there since it's…

Hi Mikko, As I stated in my post, I tried to find the join using the Record Catalog, but I couldn’t. Thant’s why I need Guru’s help. I have a correct role, not Admin and I am able to use ODBC and select the data. I still don’t understand from your answer what is the correct join. What do you mean by writing “Have you check…

Thank you, Micah

Thank you, Erick. I'll check with the implementation team about the workaround you propose. Out of curiosity - why isn't it supported out of the box? Netsuite doesn't allow to use Netsuite.com anymore for the new clients, but there are several things missing in Netsuite2.com. It is really hard to work with it. Why is it so…

Got an answer that a defect was filed: Defect 661242 : ODBC > transactionline Table Query > processedbyrevcommitColumn Included in the Results > Duplicate rows

@Mikko De La Fuente-Oracle Yes, Ticket #4581013

A little context: I am trying to calculate the balance in the bank accounts' currency via ODBC queries. So I wonder if I sum the foreignamount field - will it return the value I need. As I understand, the foreignamount is in the TRANSACTION CURRENCY, not in ACCOUNT CURRENCY so if there are any transactions in a currency…

Thank you Mikko, I still do not understand why do I have double records in the transactionLine table. Please take a look at two SQL queries from the ODBC connector: Both are supposed to select a single line (transaction = 2009, linesequencenumber = 0) The ONLY difference between the queries is that in the second query one…

Update: What I did notice, that if I select all the fields apart from processedbyrevcommit - then I am getting only one row per transaction+linesequencenumber. Even if I don't do select DISTINCT. What's going on here? Please help

Hi @Erick Dela Rosa-Oracle , Thank you very much for your help! So the only way to connect the "child" items to the "parent" item of the allocation is only by fairvalueformula? Let's say I have 2 allocation groups with the same name/formula. The first item, 100, with allocation group "AAA" and amount of $1000 is allocated…

Hi @Patrick Fresnosa-Oracle , I've read your second post as well as Articles you've mentioned. I don't see the answer to what I need. Let me please stick to your first post, your first example and clarify what I am looking for. I am working with ODBC tables. Based on the data in these tables - how can I see that elements…

@Patrick Fresnosa-Oracle Thank you very much for your detailed setup. This is very similar to what I have in my system. So my challenge is this: Based on already existing Revenue Arrangements after allocation - as in your sample: How do I know that Elements 509 and 510 were allocated from 508? And 512 and 513 were…

Sure, @Erick Dela Rosa-Oracle Here are the snapshots: I am trying to find the connection between lines 2,3 with line 1. And between lines 4,5 with line 3. Please help. Thank you!

@Erick Dela Rosa-Oracle I've done that already, but it doesn't give me the results I see in NetSuite. For example, in NetSuite I see ILS currency, in the REVALUATION table, the CURRENCY_ID value is blank for that row, and in the ACCOUNTS table that I've connected via ACCOUNT_ID the currency is USD... Maybe there is more to…

Thank you, Erick

Thanks, @Erick Dela Rosa-Oracle Very strange, but are these the tables that give me the details, that I see here: https://11111111.app.netsuite.com/app/accounting/transactions/revrec/revrecjournaldetail.nl?id=12345&line=0 I've just guessed that these are the tables, maybe there are other tables that give me the details and…

Awesome! Thank you very much, @Patrick Fresnosa-Oracle for the detailed answer. Really helpful!

Thank you Mikko, I think I understand how it works based on my research. There are Sales Orders without Billing Schedule, but for them, the UI shows one record with all the amount and the SO date as billing date.

Thank you everyone! I'll try to implement the logic in my queries

After looking at these cases (Sales Orders that do not exist in BILLING_SCHEDULE table) more carefully: I notice that they all have in common: The Date they have in UI (Billing --> Schedule) is the same as the Sales Order Date They all have one record show in UI (Billing --> Schedule) with the SO full amount. So, I guess,…

Awesome! @Patrick Fresnosa-Oracle @Jeff Pineda-Oracle Thank you friends for your help! Really appreciate it! Michael

Thank you, @Jeff Pineda-Oracle The unbilled_amount is in the TRANSACTIONS table, not TRANSACTION_LINES, so it doesn't allow me to analyze the unbilled amount per ITEMS. Furthermore, it contains the CURRENT unbilled amount and I want to build the calculation that shows trend/changes in the unbilled amount. Meanwhile I kept…

Thank you, @Jeff Pineda-Oracle for looking into this! Meanwhile I am thinking of two approaches: The first one - is to combine Invoices to Sales Orders by "CREATED_FROM_ID" column in TRANSACTION table. Something like this: For Sales Orders amount I will use this select: select t.TRANID as SO_NUMBER, t.STATUS, l.ITEM_ID,…

Thank you, @Micah Timbol-Oracle ! The links are helpful. I am analyzing the Budget vs Actual via ODBC SQL queries. Are the actuals and budget balances calculated in some table? Or they are calculated "on-the-fly" in the reports? Currently I use TRANSACTIONS and TRANSACTION_LINES tables for actuals, but I need to add…

Micah and Erick, Thank you very much for clarifications! Now I understand how this works. For my ODBC query, I will manage a manual definition similar to the report definitions. Have a great week! Michael

Just checked. Two factor not required...

Thank you Erick, I've done everything. Still getting error. My user has 2 roles: 3 (Admin) and 1022 - Dedicated role for ODBC. Attached is screen shot, showing SuiteAnalytics Connect permission for 1022 role. Also, attached a screenshot error in ODBC (with same role 1022). One of the things I noticed: when I open "Set Up…