raghav
02/13/2024, 9:59 AMSUNG YOUNG LEE
02/14/2024, 2:08 PMNawarain
02/15/2024, 6:48 AMSUNG YOUNG LEE
02/15/2024, 2:27 PMLoïc Beaujean
02/19/2024, 12:03 PMIrina Mitrova
02/20/2024, 2:13 PMjhayczee09
02/21/2024, 2:03 AMSUNG YOUNG LEE
02/22/2024, 5:03 PMSELECT
*
FROM
Transaction
WHERE
( Transaction.createddate between TO_DATE('2024-02-21', 'YYYY-MM-DD') AND TO_DATE('2024-02-23', 'YYYY-MM-DD'))
AND ( Transaction.status = 'B' )
SUNG YOUNG LEE
02/22/2024, 5:04 PMeblackey
02/22/2024, 5:34 PMraghav
02/23/2024, 3:46 PMEdgar Valdes
02/26/2024, 5:08 PMExport Dataset Definitions to SuiteQL from Workbook
You can now export your saved dataset definitions to a SuiteQL TXT file. This allows you to understand how fields are joined in your dataset so that you can build your SuiteQL queries. SuiteQL queries are available through the SuiteScript Analytic API, SuiteAnalytics Connect, and SuiteTalk REST Web Services.
To export your dataset definition to a SuiteQL TXT file, open your dataset, click Export and choose the Export dataset to SuiteQL TXT option.
Kris Wood
02/27/2024, 12:25 AMvar queryString = `
SELECT
t.entity AS customerId,
SUM(t.amountremaining) AS openBalance,
t.subsidiary AS subsidiaryId,
FROM
transaction AS t
JOIN
customer AS c ON t.entity = c.id
WHERE
t.status = 'Open' AND t.type IN ('Invoice', 'SalesOrder')
AND (c.custentity_laststatementdate IS NULL OR c.custentity_laststatementdate < ADD_MONTHS(CURRENT_DATE, -1))
GROUP BY
t.entity, t.subsidiary
HAVING
SUM(t.amountremaining) > 0
`;
// Capture result in log for now
log.debug('Input Data', query.runSuiteQL({query: queryString}).asMappedResults());
Search error occurred: Field 'amountremaining' for record 'transaction' was not found.
Field 'subsidiary' for record 'transaction' was not found. Reason: NOT_EXPOSED - Field is marked as internal for channel SEARCH
Anthony OConnor
02/27/2024, 12:42 AMSELECT
t.entity AS customerId,
t.amountremaining AS openBalance,
t.subsidiary AS subsidiaryId,
FROM
transaction AS t
Anthony OConnor
02/27/2024, 1:04 AMtoSubsidiary
which might be what you need, but based on my test account data, that's not what you want at all, its probably only used for ICJEs or something
there's also a subsidiary
column on the transactionLine table - that might just make things MORE complicated, but that's probably what you need.
there's also a foreignAmountUnpaid
on the transaction table, but I have no idea what that column even is, but it does seem to have data that looks right, again at least in my toy testdrive account - if you have multicurrency I wouldn't trust this at all 🙂Anthony OConnor
02/27/2024, 1:09 AMOpen
statusjen
02/27/2024, 6:52 PMCedric Harun
02/27/2024, 7:07 PMcustomfield
table, but i'm missing the jointure between item, customfield & file
hope there's a positive answer i did not find yet 🤞SUNG YOUNG LEE
02/28/2024, 3:08 PMraghav
02/29/2024, 4:11 PMsoTrans.entity = '113950'
AND l.id = '80'
AND shipadd.city = "Coeur D' Alene"
AND shipadd.zip = '83815'
The inverted comma in the shippadd.city is causing this error.
Invalid identifier 'Coeur D' Alene', only alphanumeric and underscore characters are allowed.
How will one handle this, in suiteql?JR
02/29/2024, 9:24 PMec
03/01/2024, 1:05 AMforeignaccts
machine list in the Currency Revaluation (Unrealized Gain/Loss) via query? It’s not the TransactionLine
join.burkybang
03/04/2024, 7:41 PMWatz
03/05/2024, 8:18 PMJian Liao
03/06/2024, 10:31 PMKris Wood
03/11/2024, 4:08 PMjen
03/12/2024, 4:52 PMRECORD IS PARENT
is ticked. Use Case: when copying a parent record, I want a generic way to identify all sublists of child records, and copy those also.Emanuel V
03/14/2024, 3:42 AMPavol
03/15/2024, 9:52 AMSELECT JSON_QUERY( '{a:1, b:2, c:3}', '$.a' ) AS value FROM DUAL
---
Expected result:
1
Dan P
03/15/2024, 9:11 PM