I'm attempting to use suiteql to get a list of lin...
# suiteqlb
Brendon Parker
08/11/2021, 7:40 PMI'm attempting to use suiteql to get a list of lines on a fulfillment. It looks like under the covers there are duplicated lines with negative quantity. What is the best way to deal with this?
Sample query:
Copy code
SELECT linesequencenumber, item, quantity, mainline, units FROM transactionline WHERE transaction = '7635'Brendon Parker
08/11/2021, 7:41 PMIn this case there are four items.
Grouping it by item seems problematic - what if the same item is on the fulfillment twice?
Why does the first line show as the main; and it is the one with units?
Filtering for only lines with units also seems problematic, as there could be an item that doesn't use unit of measure.
😬
r
Rebecca
08/11/2021, 7:46 PMThe SuiteAnswer article 27017 talks about why you get multiple lines when using a saved search and gives the formula to exclude the same. Not sure how this applies to SuiteSQL, but the information might be useful.
b
Brendon Parker
08/11/2021, 7:48 PMBrendon Parker
08/11/2021, 7:48 PMInteresting, they suggest a mod by 3. Didn't know it was that deterministic, but I'll give it a shot!
r
Rebecca
08/11/2021, 7:52 PMI know it is weird! But it works for saved searches. I’ve used it for years.
b
Brendon Parker
08/11/2021, 7:53 PMThat article is gold; thanks!
👍 1
s
Sandii
08/11/2021, 9:07 PMAssuming this is a fulfillment from a transfer order, you can also use
transactionlines.transactionlinetypem
michoel
08/11/2021, 11:04 PMOr you can filter by account type. I find that to be clearer (and probably more future proof) than mod 3
👍 1
b
Brendon Parker
08/12/2021, 1:20 AM@Sandii it’s not a transfer order in this case.
@michoel thanks. I’ll look into that. Modding seems like a hack. Like it will work great until it doesn’t.
Brendon Parker
08/12/2021, 12:19 PMFiltering by
iscogs = 'F'c
Clay Roper
08/12/2021, 2:15 PMHonest question that I don't know the answer to: will
iscogs = 'F'b
Brendon Parker
08/12/2021, 2:16 PMMy full query params are
Copy code
itemtype IN ('InvtPart', 'Kit', 'Assembly')
AND iscogs = 'F'
AND taxline = 'F'👍 1
r
Rebecca
08/12/2021, 2:19 PMNice solution. Does the shipping line need to be handled?
b
Brendon Parker
08/12/2021, 2:20 PMIn my case, no.
7 Views