Quick Search for Orders

LeoC · February 24, 2020, 5:21pm

Hi all!
I’m tryin to do a quick search for finding a sales order.

The parameters of the search will be:
Customer Name or Customer ID
ShipNum or ShipDate (between)
OrderDate (between)
Invoice Number and date (between)
Part Number or description or typecode (contained in the order)
NeedBy date (between)
Entry Person (of the order)
‘Commessa’ (a custom field on the orderhed)
PONumber of the order.

As you can see the parameters are a lot…
I tried to create the BAQ for the search:

I started with a first level with ‘OrderHed’ and the subquerys (InvHed, Customer, Part, ShipHed and ShipDtl). Then I used the ‘SubQuery Critera’ but with that I can’t display the fields of the subquerys.

I tried then to do the subquerys directly on the first query level and with the ‘table relations’ I was able to display all fields of my interest.

The problem now is that if I search an order for a customer, the BAQ will not display an order if doesn’t have let’s say an invoice already saved.
I want that the query still displays me the order just with the invoice fields empty…

Is this possible?

Thanks!

ckrusen · February 24, 2020, 5:43pm

The tables related to OrderHed should use Outer joins (All rows from OrderHed ). That way an OrderHed without any Invoices will still show.

example:

In the following BAQ, All Vendors are returned, whether they have a TermsCode set or not.

If that was an inner join, I’d only get Vendors that had the TermsCode set

LeoC · February 25, 2020, 2:41pm

Oh my god that was so silly by me, thaks as always Calvin…

I still can’t make it work tho…
When I searched without a PartNum I had all the lines filled with the same order but with all the different Parts that it had, so I selected ‘Distinct’ at subquery level.
Now one problem is solved but I still have rows that shoul not get out…
For example I selected the CustID 41 but I have rows for Customer that are not the 41.

I’ll upload the BAQ so you can see what I’m talinkg aboutdldld.baq (67.8 KB)

Hope that I explained it well and that yu can help me,
Thanks!

ckrusen · February 25, 2020, 3:24pm

It seems like you want specific combination of info. This correct?

I’ll try to look at your BAQ and see if that’s clearer.

LeoC · February 25, 2020, 3:34pm

Yeah
In short I need to find an order (OrderNum) by every info connected to it (for ex: ponum, date of the invoice, packslip number,…)
I’ll use this baq for a Custom Search on the SalesOrder Entry

ckrusen · February 25, 2020, 3:36pm

It looks like none of you parameters have the “Skip Condition if Empty” checked.

If this is not checked, then the “blank” value is used for searching.

ckrusen · February 25, 2020, 3:38pm

Also, Did you mean to make the OrderHed to subquery3 sub query an inner join?

ckrusen · February 25, 2020, 3:51pm

And another thing… Your Param PackNum is of type nvchar. It should be of type int.

If you really need to search on “matching” PackNums (like 1*2345 to find 12345, 13345, 14345, etc…), then you need to make a calc field to represent the PackNum as a string.

LeoC · February 26, 2020, 5:08pm

Yeah sorry i forgot to do that.
I tried already to check ‘Skip Condition is Empty’ on all the parameters but then I can’t use the parameter as an expression…It vanish from the ‘Parameters’ section when selecting ‘specified expression’.

ckrusen · February 26, 2020, 5:17pm

From your BAQ the search parameters will be:

You want to be able to search on any combination of entered values, with fields left blank being ignored?

LeoC · February 26, 2020, 5:23pm

Yeah exactly

ckrusen · February 26, 2020, 7:41pm

I think the main problem is that in or for any of the entered conditions to be met, you need to build a dataset containing every combo of OrderHed and the tables with the criteria to filter.

Then from this HUGE dataset, select only records that match your filters.

I’ll keep thinking about it. Hopefully someone comes along and shows us how easy it actually is.

tanner · February 26, 2020, 8:00pm

I must admit I’m not a BAQ expert but I don’t think you want to specify the parameters within the BAQ itself. You probably want to make the BAQ as generic as possible and then in quick search maintenance is where you would specify the criteria

LeoC · February 27, 2020, 9:20am

Yeah I hope that too.
The option of the Dataset doesn’t actually inspire me

Thank anyway for your support Calvin!

ckrusen · February 27, 2020, 4:11pm

I was able to make a BAQ that does everything you want, but it is virtually unusable as it is so slow.
Even timing out every now and then (depending on the criteria entered).

I think you best bet will be to make independent BAQ Searches. Unless you really need to be able to find an order based on odd combinations of inputs like

ShipDate between X & Y AND InvoiceNum = N AND PartNum like P

LeoC · February 28, 2020, 8:04am

I tried already to ask if I could do little indipendent BAQs but they said that they needed the big one…

Could you link me the BAQ that you created?

ckrusen · February 28, 2020, 2:44pm

One of the things that makes the data sets so big, is showing records for the combinations of all the conditions entered - specifically blank conditions

If you have customer XYZ123, and there exists (3) separate orders, with 100 lines each, and only search on the CustID, you’d get 300 results.

All of this is with the assumption you want to see the matching values, in the results. If you leave the PartNum blank, then all 300 records “match”. For example.

OrderNum   CustID  CustName     PartNum    PartDesc        PackNum   ShipDate
========   ======  ===========  =========  ==============  =======   =========
1234       XYZ     XYZ Corp.    WID-001    Widget 001       5578     1/1/2020
1234       XYZ     XYZ Corp.    WID-001    Widget 001       5585     1/15/2020
1234       XYZ     XYZ Corp.    WID-002    Widget 002       5578     1/1/2020
1234       XYZ     XYZ Corp.    ACC-999    Accessory Kit    5578     1/1/2020
2345       XYZ     XYZ Corp.    WID-200    Widget 200       6543     2/1/2020
2345       XYZ     XYZ Corp.    WID-250    Widget 250       6543     2/1/2020
2345       XYZ     XYZ Corp.    SK-9901    Sales Kit XYZ    6543     2/1/2020

Rows 1 and 2 of the above example would be fore the same order line that was shipped on two packers.

If the results could be limited to just showing the OrderNum, It might be usable.

LeoC · February 28, 2020, 3:14pm

Yeah it’s my problem for now.
If I don’t create the PartNum and PartDescr fields for the search all is fine.
When I add those 2 the query will become imusable cause I’d get one line for each part of the order…

ckrusen · February 28, 2020, 3:44pm

Not just a line for each OrderDetail, but a line for every different Packer or Invoice that references that OrderDtl line too.

In the following, the first highlighted sectionis for one line of the order. It has shipped on one packer, but invoiced 4 times.

The second highlighted section is for another line (with the same P/N), on the same order. It was shipped on the same packer as highlight #1, but invoiced at a later date.

The more fields you enter the faster it actually runs!

Another issue you’re going to run into, is that while you can set the Param for InvcNum as “Skip condition …”, that won’t work, because the parameter for field defaults to 0 even when left blank.

ckrusen · February 28, 2020, 3:55pm

Here’s the best I could do:

E10H-DLDLD_5.baq (114.3 KB)

Running it with no parameters entered (or with non-limiting ones like FromDate 1/1/1966) will cause it to timeout, and probably not return all the values.

Edit

I just used the Parameters as the condition, not an expression adding the asterisk to be used as a wild card. Because using it in an expression overrides the “Skip condition…” property of the parameter. Leaving the parameter blank makes an error.