I want to incorporate the data tag search into my quick search which will replace the base search.
I can’t figure out a good way to do this since each data tag is on a separate row in the SysTag table. If I want my BAQ to pick up all these records, then when the search is not performed on the data tag (say on PartNum) it will return multiple records for each part ( if there are multiple tags for that part).
Has anyone incorporated these into a quick search and if so, how.
Or, does anyone know if it is possible to add a field to a BAQ that would concatenate all of the records from a child table? Then, I could just search on that field. Something like this…
How many data tags are you using? I am envisioning a BAQ to check for each one against each record, which isn’t too hard to do. Does it need to be a concatenated field or can you have a separate check column for each?
I am not following. It will be under 5 tags per part record. We use the tags to have short lists of parts returned, but a part can belong to a few lists.
How do you get to the resulting record you have from a BAQ without changing the table structure? I would think I can just do a calculated field to combine all of those values if I had that as my record, but I can’t get to the point of having one record return per part with essentially an array of values from the child (tag) table.
So each part can have up to 5 data tags? You could definitely create a query that would return 6 columns (1 is partNum, the other 5 are your unique data tag values). By using subqueries, you can return a single part record with any resulting data tags (by column).
As an example, I do something similar to display BP Holds on records. For each Part Lot in this example, I am returning a value from a subquery where it exists.
Yes, ForeignSysRowID on SysTag = SysRowID on Part.
I am not overly familiar with using subqueries in BAQs. How can I return a value from from the sub query which is the 1st, 2nd, 3rd, etc. occurrence of this record to return to these 5 difference columns?
So basically, you will have your top level query with Part as the first table. You will need 5 subqueries. Your first inner subquery will be the SysTag table. On that table, you will put a table criteria to be your first condition, which will be whatever field you are storing “Red” in (or whatever). In this subquery, you will display the column that holds the value of the data tag AND also display the ForeignSysRowID.
For each different possible column, you will want to create a new subquery and do the same thing as above, just filtering each one by that criteria.
When all that is done, on your top level query, bring in each subquery you created. Join them to the part table via left join on ForeignSysRowID to Part.SysRowID. Then, display the PartNum and from each subquery, the column that describes the data tag.
You will then have a single row per part and if the part has a data tag, it will display in the proper column.
