My current query is
```select tal.amount,
...
# suiteqln
Nim Sadeh
10/12/2022, 1:56 AMMy current query is
Copy code
select tal.amount,
cr.exchangerate,
tal.transaction,
t.trandate as date,
t.exchangerate
from transactionaccountingline tal
inner join transaction t on t.id = tal.transaction
inner join transactionline tl on tl.transaction = tal.transaction and tal.transactionline = tl.id
inner join subsidiary s on s.id = tl.subsidiary
inner join currencyrate cr on cr.effectivedate = t.trandate and cr.transactioncurrency = t.currency
where t.postingperiod = '<period>'
and tal.account = '<account id>'
and t.subsidiary = '<subsidiary id>'
and cr.basecurrency = '<parent currency>'b
battk
10/12/2022, 5:02 AMmight want to try BUILTIN.CONSOLIDATE
battk
10/12/2022, 5:14 AMthis is one of those times where you may actually want to create a query object and use Query.createColumn to make your query first
battk
10/12/2022, 5:16 AMit allows you to use the field context to use currency consolidation
battk
10/12/2022, 5:17 AMyou can later use Query.toSuiteQl if you just wanted the suiteql
n
Nim Sadeh
10/12/2022, 2:43 PMIt’s rather unclear to me how to actually use
BUILTIN.CONSOLIDATE Copy code
select BUILTIN.CONSOLIDATE(tal.amount, 'INCOME', '<parent_curr_id>', '<sub_currency_id>', '<sub_id>', '<period_id>', '<book_id>'>)
from transactionaccountingline talNim Sadeh
10/12/2022, 2:47 PMAlso if possible I’d love to be able to consolidate more from first principles rather than use a builtin
b
battk
10/12/2022, 2:51 PMi would recommend getting a dataset in the ui to get the amount you wanted
battk
10/12/2022, 2:51 PMbattk
10/12/2022, 2:51 PMthen load the dataset to log its suiteql if you really wanted suiteql
n
Nim Sadeh
10/12/2022, 2:54 PMI haven’t tried workbooks yet, thanks for the pointer!
b
battk
10/12/2022, 2:55 PMcalculating the consolidated exchange rate yourself involves understanding the different consolidated exchange rate types
battk
10/12/2022, 2:56 PMyou are very lucky if you can just use the exchange rate from the transaction, that basically means no consolidation
n
Nim Sadeh
10/12/2022, 2:57 PMYea. For this instance I am consolidating a single expense account, so I am using the weighted average of the exchange rate on the day of the transaction (
transaction.trandateb
battk
10/12/2022, 2:58 PMthere is a table for the consolidated exchange rates that you should be using
🙌 1
n
Nim Sadeh
10/12/2022, 2:58 PMOh really??
Nim Sadeh
10/12/2022, 3:01 PMIs that on a SuiteQL table somewhere?
Nim Sadeh
10/12/2022, 3:03 PMFound it!
consolidatedexchangerateNim Sadeh
10/12/2022, 3:12 PMOK that was easy, just consolidated the income statement correctly. Thank you!
3 Views