Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to compare two fields from two different tables.
Hi
Some more details would be helpful.
Normally you would need to join the tables and then compare if done in script.
But you could do in front end if you are using on a chart, with set analysis.
Mark
In customer table,
where Document Type =2 then amount is sales_amount
In purchase table,
invoice = Document No in customer table then Amount in purchase table is purchase_amount
Purchase
Code | invoice No | Date | Amount |
---|---|---|---|
a1 | QA/001 | 15/1/2010 | 45 |
b2 | SA/002 | 8/2/2010 | 85 |
c3 | LA/003 | 7/1/2011 | 95 |
a1 | NA/005 | 3/5/2014 | 65 |
b2 | DA/005 | 27/4/2015 | 15 |
c3 | BA/005 | 19/3/2012 | 24 |
a1 | KA/006 | 6/6/2013 | 72 |
b2 | ZA/007 | 17/1/2015 | 38 |
a1 | EA/008 | 7/8/2014 | 18 |
a1 | GA/009 | 16/9/2015 | 91 |
b2 | HA/010 | 5/9/2012 | 48 |
c3 | JA/011 | 6/8/2013 | 46 |
a1 | WA/012 | 24/03/2016 | 67 |
b2 | IA/013 | 31/05/2015 | 92 |
c3 | TA/014 | 23/05/2016 | 39 |
Customer Table
Code | Document No | Document Type | Amount | Date |
---|---|---|---|---|
a1 | RA/001 | 1 | 10 | 1/1/2010 |
b2 | SA/002 | 1 | 20 | 2/2/2010 |
c3 | LA/003 | 1 | 30 | 1/1/2011 |
a1 | NA/005 | 2 | 50 | 5/5/2014 |
b2 | DA/005 | 3 | 60 | 10/4/2015 |
c3 | BA/005 | 4 | 70 | 9/3/2012 |
a1 | KA/006 | 5 | 80 | 6/6/2013 |
b2 | PA/007 | 2 | 90 | 8/1/2015 |
a1 | FA/008 | 2 | 45 | 7/8/2014 |
a1 | GA/009 | 2 | 65 | 6/9/2015 |
b2 | HA/010 | 2 | 82 | 4/9/2012 |
c3 | JA/011 | 6 | 15 | 6/8/2013 |
a1 | YA/012 | 7 | 24 | 25/03/2016 |
b2 | IA/013 | 6 | 62 | 30/05/2015 |
c3 | OA/014 | 8 | 34 | 21/05/2016 |
HI,
If i am understanding correctly you could load both tables. then join the fields back you want to purchases,
So
Customer:
Load
*
From Customers;
Purchases:
Load
*
From purchases;
Left Join(Purchases)
Load
[Document No] as [invoice No]
Amount as [Sales Amount]
Resident Customer
Where [Document No] = 2;
Mark