You’re offline. This is a read only version of the page.
485
Hello,
I don't know X++ but I know a little SQL... I have this query that I need to write in X++ can someone please help.
Query:
SELECT
INVOICEID
,INVOICEACCOUNT
,INVOICINGNAME
,CUSTSETTLEMENT.DUEDATE
,CUSTSETTLEMENT.CREATEDDATETIME
,CUSTSETTLEMENT.TRANSDATE
,CustSettlement.SETTLEAMOUNTREPORTING
,INVOICEAMOUNT
,PAYMENT
,INVOICEAMOUNTMST
FROM CUSTINVOICEJOUR join CUSTTRANS ON CUSTTRANS.INVOICE=CUSTINVOICEJOUR.INVOICEID AND CUSTTRANS.AccountNum = CUSTINVOICEJOUR.InvoiceAccount
and CUSTTRANS.TransDate = CUSTINVOICEJOUR.InvoiceDate
and CUSTTRANS.Voucher = CUSTINVOICEJOUR.LedgerVoucher
join CUSTSETTLEMENT
on CUSTSETTLEMENT.TransCompany = CUSTTRANS.dataAreaId
and CUSTSETTLEMENT.TransRecId = CUSTTRANS.RecId
and CUSTSETTLEMENT.AccountNum = CUSTTRANS.AccountNum
WHERE CUSTSETTLEMENT.DUEDATE = CUSTINVOICEJOUR.DUEDATE
AND CUSTSETTLEMENT.DUEDATE between'2018-09-01' AND '2019-08-31'
AND CUSTSETTLEMENT.TRANSDATE <'2019-08-31 0:0:0'
AND CUSTINVOICEJOUR.INVOICEID LIKE 'ACP%'
AND CUSTINVOICEJOUR.INVOICEACCOUNT='XXXXXX'
*This post is locked for comments
change the label of fields on forms
Hey Imran, thanks much for your help.. the query worked perfectly. I have one problem though,
When designed my form I need to change the field labels. like due date should say: "From Date" and To date. how can I do that? see image with the issue.
while select * from FROM CUSTINVOICEJOUR
join CUSTTRANS where CUSTTRANS.INVOICE== CUSTINVOICEJOUR.INVOICEID
&& CUSTTRANS.AccountNum == CUSTINVOICEJOUR.InvoiceAccount
&& CUSTTRANS.TransDate == CUSTINVOICEJOUR.InvoiceDate
&& CUSTTRANS.Voucher = CUSTINVOICEJOUR.LedgerVoucher
join CUSTSETTLEMENT
where CUSTSETTLEMENT.TransCompany == CUSTTRANS.dataAreaId
&& CUSTSETTLEMENT.TransRecId == CUSTTRANS.RecId
&& CUSTSETTLEMENT.AccountNum == CUSTTRANS.AccountNum
&& CUSTSETTLEMENT.DUEDATE == CUSTINVOICEJOUR.DUEDATE
&& CUSTSETTLEMENT.DUEDATE >='20180901'
&& CUSTSETTLEMENT.DUEDATE <='20190831'
&& CUSTSETTLEMENT.TRANSDATE <'20190831'
&& CUSTINVOICEJOUR.INVOICEID LIKE 'ACP*'
&& CUSTINVOICEJOUR.INVOICEACCOUNT=='XXXXXX'
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (