Is there a way to, in effect, build a workbook tha...
# generaln
Nate Peterson
10/08/2020, 12:43 PMIs there a way to, in effect, build a workbook that leverages transactions and then left joins to other records? I can see by default there is a full join and can then limit based on criteria...but then I lose visibility to transaction records that don't have existing data in the joined record. PO lines to Vendor Item costs is the real word example. Any thoughts?
e
Era
10/09/2020, 8:37 AMhi Nate,
you can have a "composite criteria", which would have 2 elements, with OR between them:
((one for what you are interested...and where the id of both "joined"-tables is not null) OR (the other one for where the "joined"-table, has the id null-value/s))
n
Nate Peterson
10/14/2020, 10:22 PMHi Eva...I tried that but it was filtering the results out...let me try again and will post back if I am still having issues
Nate Peterson
10/16/2020, 3:20 PM@Era Here is what I am seeing...let me know if not clear. Trying to see all PO lines but then merge in vendor purchase price if setup on the item master record
Nate Peterson
10/16/2020, 3:20 PMmessage has been deleted
Nate Peterson
10/16/2020, 3:21 PMwhen I try to bring in a validation of the vendor purchase price of empty I get no records...if I remove the logic I get records, but then get duplicate PO lines for each vendor purchase price setup
Nate Peterson
10/16/2020, 3:23 PMmessage has been deleted
Nate Peterson
10/16/2020, 3:24 PMthe formula in the vendor check is here:
CASE
WHEN {entity^vendor.companyname} = {transactionlines.item^item.itemvendor.vendor#display} THEN 1
ELSE 0
END
Nate Peterson
10/16/2020, 4:00 PMI don't think there is a way to do this since I am joining a many to one to a one to many relational model. I think I need to merge two datasets via exports and do it in Excel
e
Era
10/16/2020, 5:17 PMhi Nate,
first, in workbooks, you always have left joins
Era
10/16/2020, 5:18 PMlike the joins you do in UI, are only "left outer joins"
Era
10/16/2020, 5:19 PMonly in SuiteQL, you can leverage any join...
Era
10/16/2020, 5:21 PMthis means, the only way you can do it, is to...have 2 datasets and then merge the results
Era
10/16/2020, 5:23 PMmy suggestion would work, only in case you would have a full join(which you can't have in UI) - cause then, basically, you could select from the "full join", only what would be required, with the "composite criteria"(or formula)
Era
10/16/2020, 5:25 PMreading your initial post, I saw you mentioned the "full join"...now I saw/realized, you wanna do it in UI - which has the limitation of using only left-join
n
Nate Peterson
10/16/2020, 5:26 PMI am not familiar with SuiteQL is this a add on feature or a 3rd party tool?
e
Era
10/16/2020, 5:28 PMit's NS, but not supported in UI.
see HelpCenter: https://nlcorp.app.netsuite.com/app/help/helpcenter.nl?fid=section_156257770590.html
Era
10/16/2020, 5:29 PMmessage has been deleted
n
Nate Peterson
10/16/2020, 5:29 PMthank you
👍 1
3 Views