I have a moderately complex BAQ used by a QuickSearch which has a Top Level and two Sub-queries that retrieve and consolidate data from OrderHed and several related tables. The first subquery is called “OrderPart_SQ” and the second is called “OrderStatus_SQ”.
OrderPart_SQ aggregates parts from OrderDtl to a single string using the string_agg function. OrderStatus_SQ determines the order’s status based on a significant amount of detail including holds, RTF, existing packers and invoices. Each return their respective values to the Top Level query which contains several standard fields and the two calculated fields.
When I build the QuickSearch, I specify the various criteria. The parts field shows as “Calculated_PartList”, but the status field defaults to “OrderStatus_SQ_Calculated_OrderStatus”. If I try to save the QS like this, I get an error which states “The table/field is not valid.” To fix this, I simply re-select Calculated_OrderStatus from the Criteria Column drop-down and the record can then be saved. My problem is that that when I pull up the QS again, it reverts back to it’s previous state as shown here:
As stated earlier, in order to save the QS, I have to re-select Calculated_OrderStatus.
When attempting to use the QuickSearch, I can specify a Part and get the expected results. When specifying an Order Status, however, I always get ALL orders. My guess is that the whereClause is ending up blank somehow. Curiously, if I use Actions > Test Quick Search, it works perfectly.
When looking at the SQL data in Ice.QuickSearchCriteria, the columns appear as shown here:
Note that I’ve place a red box around the subquery value which does not work correctly, and a green box around the subquery value which does work.
BTW, I did see a post where someone resolved this by directly editing the SQL data, but this is not an option at my company for data integrity reasons.
Please let me know if you need any additional information, and I’ll do my best to provide a response.
Thanks,
Tony G.