nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Modify queryText with subset of data from outer query

canutri

I have a query having a result set of a Lot # and a Component Lot #. 1 or more Component Lot #'s can be associated with a Lot #. The following table depicts a sample data set. Lot # Component Lot #ABC123 XYZ987ABC123 EFG454ABC123 PBJ999DEF456 PDQ001DEF456 DNF225 For each Component Lot # I need to find quality values in seperate databases - I do not know which database I will find the lot # in so I have to include all Component Lot #'s in the query. To complicate this I need to provide one row for each Lot # showing the Lot #, and a reading for 3 different quality values. To get a single row for each Lot #, I'm using a Group to group on Lot #. My attempt here is to build a commad deimited string of Component Lot #'s which will be used 3 inner queries for the 3 quality value columns. I was hoping to use the comma delimited string of Compoent Lot #'s and use it the beforeOpen event of the inner query, but that isn't working as I expected. My question is this, with an inner query being on the Group row of the outer query, is it possible to build a string of the Component Lot #'s for this Group to be used by the inner query. Once the inner query has executed for the 2 or 3 Component Lot #'s, I want to clear the string being built for the next Group processed by the outer query. The string is being built and cleared using a PersistentGlobalVariable. Thank you, Daron

Find more posts tagged with

Comments

canutri

I think I may have it figured out. I need to build the PersistentGlobalVariable string in the outer query table row onCreate event and clear the string when used in the inner query's beforeOpen event.

canutri

I don't have this figured out yet

From the sample table data above, I want to have 2 rows only for ABC123 and DEF456 which I have using a Group on the Lot #. In cells adjacent to the Grouped Lot #, I have 3 cells each having an inner table. The query needs to be ammended before being executed to search a different database using the Component Lot #'s; XYZ987, EFG454 & PBJ999 for the first row of the outer table and PDQ001 & DNF225 for the second row. I thought I could use the beforeOpen event to alter the queryText for each row to include only the Component Lot #'s desired. i.e this.queryText = this.queryText.replace("/* IN predicate */", inPredicate);Where the query text would be "SELECT instrumentId, max(reading) FROM myDB WHERE /* IN predicate */ GROUP BY instrumentId"and inPredicate would be built as " IN ('XYZ987', 'EFG454', 'PBJ999')" for the first row and " IN ('PDQ001', 'DNF225')" for the second. Unfortunately, I'm having problems controlling the list of Component Lot #'s in the PVG. It's either empty or contains all Component Lot#'s. Is there another approach? What am I missing about the event sequences? I'm trying to use the outer tables row's onCreate to build the list of Component Lot #'s. Then, in the outer table's Group row I'm modifying the inner query and then clearing the PVG for the next group of Lot #'s/Component Lot #'s. Daron

bgbaird

Is the structure of the databases that contain the quality values such that they can be normalized? Can you always get "Component Lot #" and "Quality Value" from them? If so, you could create a Union Data Set of all the value data, and then either join the datasets, or bind them in the table. <del class='bbc'>I'm attaching a sample. Even though it uses the same table for both children, they return different result sets.</del> I would attach a sample, but it seems I've uploaded too many examples. I'll see what I can figure out. Brian

bgbaird

Here is a link to my example, but it will expire in 2 weeks. <a data-ipb='nomediaparse' href='https://file.ac/ip3U2CgqYPk/'>https://file.ac/ip3U2CgqYPk/</a> Brian

canutri

Hi Brian, As a programmer, I believe I've fallen victim to trying to do too much with javascript and not think things through using the designer. I've been using javascript more lately. The joined data set should do the trick. I'm embarrased to say that I've used them before in similar circumstances. I've got it working, but the 2nd data set is fetching all rows and not just limited to the Component Lot's found in the 1st data set, so I may play around with buidling an IN clause. This should help speed up the query and minimized the in-memory hit for the joined data set. Thanks for your input. I can now move on with my project after 3 days of pounding my head against my desk. Daron

canutri

Good new / Bad new... The joined data set is working. Although I'm concerned about in-memory processing joining the full resultsets from 3 seperate databases to my Certificate Components, I have the correct instrument readings required. Unfortunately, the test metrics validation needs to be changes to get the correct effect(s) on the Certificate when the instrument readings are out of spec. When an instrument reading is out of spec the cell and value color is altered to highlight the error (This is easily accomplished using Highlights), however, I was using scripting to set a PGV with an "error" value which activates a watermark - "Quality Review Required" and also X-out the signature line. Further complicating this is that the out of spec value is conditioned on the aggregate - average or max - for Lot #. I believe the watermark is rendered prior to the table's aggregate. For clarity, the watermark which is a background set on a single column/row cell for which all other content is contained in. Any thoughts on how I can get the watermark to show when an aggregate is "out of spec"? Daron