Filtering a Parent List Based on Values in a Child List

[Pizzadude]

In a recent Pre-Release, Alpha introduced a restriction in the List control, if the List is populated by a SQL (in this case MySQL) query:

If the List has a Detail View, GROUP BY may not be used unless the List is read-only.

This makes sense as each row in the List may represent more than one row from a table, and the Detail View can only hold data from one row.

My dilemma is that I have parent/child lists, and I need to filter the parent based on values in the child, and without GROUP BY, the parent list returns duplicate rows.

Here is a video https://www.screencast.com/t/TcuoVXN7wcQs

I have attached the component including the tables. To use it you will have to set up the Connection Strings in both lists.

Note that this is a very simple UX. My application is much more complicated. The parent list must have a detail view, and it must be updatable.

One thought is that when the parent list has a detail view, it MUST return the Primary Key, in which case it would be reasonable to use GROUP BY.

I should mention that DISTINCT has the same restriction.

I am using Pre-Release Build 4942-5065

TestParentChild.zip

[Davidk]

Re: Filtering a Parent List Based on Values in a Child List

Your video won't run unless Adobe Flash is installed... and there are 2 pieces of crap software I'll never again install on my Windows machine.... Adobe Flash and iTunes. Can you post in another format?
You're actually running a SELECT... I thought you were running a SQL Query.

What you want to do may make sense from a business logistics sense... but from a data sense... it doesn't.

Considering what you want to search for... StopDate=03/21/2018 and PickupOrDelivery=P then you're looking for Parent Key=4. Parent Key 4 has a Pickup=Chicago and a Deliver=Denver.

This means you want to show only Parent=4 in your Parent List... after a search... is that right?

Do to this I'd go after the Child records... find what I want... get the Parent key(s), and then filter the Parent List based on those keys.

Change your Parent select to this...

SELECT DISTINCT parent.Pickup, parent.Deliver 
FROM parent parent
	 INNER JOIN child child
		 ON  parent.Parent_PK = child.Parent_FK

Another change I made... in the Parent Detail View, Table Properties, the Parent allows updates... but the Child Allow update is turned off.

[RichCPT]

Re: Filtering a Parent List Based on Values in a Child List

I don't think you need to JOIN the tables nor do you need DISTINCT or GROUP BY to get the type of filtering that you want. It is not apparent at first, but the SQL Genie in Alpha does understand the use of a SQL "IN" clause and a subquery on the right-hand filter expression. When you enter your filter expression for the parent table (Truck or Load table) you specify its primary key field on the left-hand side, you select "is in" for the "Operator" and then in the right-hand side you enter the subquery to find child records containing the date you are looking for and to return just the parent ID. When putting in the sql subquery just click the up-arrow to open the "Specify a Value or Expression" window. Click on the "SQL Select Statement" tab and you can enter your query by hand or open that secondary sql builder that is there. In my example, I entered the sql that you see between "((" and "))" from below.

Sorry my field names and table names do not match yours. But for my "parent" table, which correspond to your "load" or "truck" table, the complete SQL looks like this:

SELECT doDocument_SysID, doDate_Start 
FROM Document 
WHERE doDocument_SysID IN 
((SELECT ddDocument_SysID FROM DocumentSignatures  
  WHERE ddDocument_SysID = doDocument_SysID  AND  
  convert(date, ddSignatureDateTime) = :gSrchChildDate  OR :gSrchChildDate IS NULL    
))  
ORDER BY doDate_Start DESC

For this simple example, I am just displaying the primary ID in the parent table and date. I have defined an Argument, ":gSrchChildDate" that is bound to a field on the UX. In my database I am using a date/time field so I must strip the time part out using the "convert(date, ...)" function before testing for equality to the entered date. To filter the primary list, I added a button after the seachDate field. The button calls the LIST Refresh method using actionJavascript. Don't use the "Search of Filter List" actionJavascript here, just use the "refresh" list method (server-side). The child list is setup with a query that shows all the child records for the chosen / selected parent record. The child list is linked to the parent, so it refreshed when a new row is selected in the parent.

Notice in my subquery it links the child back to the parent via the "??Document_SysID" fields.

[Pizzadude]

Re: Filtering a Parent List Based on Values in a Child List

Do to this I'd go after the Child records... find what I want... get the Parent key(s), and then filter the Parent List based on those keys.

David, this is a possible solution. But I am really looking for the base list query/SELECT to always return one row for each load.

Rich, I had attempted to use a sub query, but I did not use the the IN clause. That makes a difference!

My ultimate goal is to join the parent to the child in the Parent list, so I can have child values to use in filters, and always return one row per load in the parent table.

I have been attempting to write a join that contains a sub query within the join, but no success yet.


How can I modify this so it only returns one row in the parent?

SELECT parent.Pickup, parent.Deliver, child.Child_PK, child.PickupOrDelivery, child.StopDate
FROM parent parent
	 INNER JOIN child child
		 ON  parent.Parent_PK = child.Parent_FK

[Davidk]

Re: Filtering a Parent List Based on Values in a Child List

It's not at all clear what you want to do. You post "return one row for each load"... what do you mean by load?

You also post "so it only returns one row in the parent"... and then post a JOIN SELECT for parent/child tables where the data has multiple children per parent. How could that possibly return one row?

If I could think of your process in the oddest way... it seems that you want the parent list to display grouped parent rows (even though you're including columns from the child table)... make a selection and show the children... then make a selection from the children... and then re-filter the parent based on the child selection so that it now shows only 1 row.

Is this what you want to do?

Maybe it would help if you explained why you want to do whatever you're trying to do... what's it for? It's not making any sense.

[RichCPT]

Re: Filtering a Parent List Based on Values in a Child List

I have to somewhat ignore the exact question about "how do I get one row from the query with the JOIN?" I'd have to agree with David here that you are perhaps asking for the impossible or at the very least you know something about the content of the child records that we do not know or are not assuming.

More precisely, it seems that you are assuming that either only one child record meets the search date criteria (seachDate = child.stopDate) or that you are assuming that all child records that do meet the search date have the same value for the "child.PickupOrDelivery".

The query I gave you answers the original question about how to find the trucks (or loads) that have a pickup or delivery date on a particular date. That query should be simple enough for Alpha to digest and let the LIST remain editable. But I did not try editing records, so I cannot be sure.

You cannot return just a single value of "Pickup" or "Delivery" for the query because qualified child records could have both. Also, it seems to me that a "stop" could include both actions: "Pickup" and "Delivery". Therefore, there should be two columns in the database. I would create a "pickup" column which defaults to zero, but contains a 1 if there will be a pickup. I would create a "Delivery" column that defaults to zero and contains a 1 if there is a delivery. In the query of parent records you keep it as I said before with Alpha's "IS IN" subquery, but you also do a JOIN to a SELECT/SUM query to get the count of "Pickup" and "Delivery" columns for child record with matching the search date. I prefer putting these types of SELECTS into the database as a view. It keeps things simpler for the Alpha SQL query builder (as it can be told to treat that part of the query as a view-only table) and it keeps the builder from choking on complex SQL.

[dbinns]

Re: Filtering a Parent List Based on Values in a Child List

I cannot understand what you are trying to do here either. What is the actual intent?

You have a list of Loads with destinations and you want to find the destination?

I may be missing something but I don't know why you need the don't have all of the delivery data in one table (load pickup delivery) you can still use a locations table to select them from?

I think I'm confused, really don't get what you are trying to do.

[Pizzadude]

Re: Filtering a Parent List Based on Values in a Child List

Sorry that I have been confusing. I will try to clarify.

I am not trying to limit the number of records returned by the query, merely to limit the number of rows "displayed" in the list control to one row for each "load".

Using this statement in the parent list control, you would expect it to return one row for each matching record in the child table, where parent.Parent_PK = child.Parent_FK. If there are two matching records, the parent list would display two rows, both of which contain the same value for parent.Parent_PK.

SELECT parent.Parent_PK
FROM parent parent
	 INNER JOIN child child
		 ON  parent.Parent_PK = child.Parent_FK

The list control would display:
Untitled1.png


I want the parent list to display only one row. Ordinarily, it is my understanding that this can be accomplished by adding GROUP BY to the above statement:

SELECT parent.Parent_PK
FROM parent parent
	 INNER JOIN child child
		 ON  parent.Parent_PK = child.Parent_FK
GROUP BY parent.Parent_PK

Now the list control would display:
Untitled2.png


However, the use of GROUP BY is no longer allowed if the list control has a detail view.

So I am looking for a way to "display" only one row in the parent table, for each Parent_PK returned to the Parent list control.

[Davidk]

Re: Filtering a Parent List Based on Values in a Child List

However, the use of GROUP BY is no longer allowed if the list control has a detail view.

That's not quite the whole case. If the List has a Detail View... and... GROUP BY is used in the SELECT statement... then the primary table in the Detail View must be set to Read-Only... sometimes the secondary table as well.

This makes sense as the SELECT statement is returning several rows per Group... therefore how does the Detail View know which set of details to work with. It can't know. You couldn't update the List data in this case.

Your most recent SELECT statement with a GROUP BY and the table set to Read-Only will work. However, your original SELECT included fields from both the parent and child tables... and therefore could never be grouped down to one row per key. Multiple rows would always display because the data in all columns is different per row... even though the key is the same.

[Pizzadude]

Re: Filtering a Parent List Based on Values in a Child List

Yes, the use of GROUP BY is no longer allowed if the list control has a detail view, unless the list is read-only.

However, for a long time now I have been successfully using GROUP BY in a list that has a detail view, and the list is not read-only. It has been flawlessly updating the correct record in the table.

The reason it has worked is because the list is required to return the primary key if there is a detail view. Therefore the detail view will update the correct row, because it uses the primary key to identify the row to update. Multi-select is also not allowed where there is a detail view.

I understand that if the join uses a column from the parent other than the primary key, (I cannot think why that would ever be desirable) it would cause problems. But that is the only scenario I can think of that would cause problems.

So I am looking for a solution. I want to retain the detail view, and all the benefits it provides, including updating the list control values. I also want to have the join to the child table.

[RichCPT]

Re: Filtering a Parent List Based on Values in a Child List

Joe, incase you missed my last two paragraphs in post # 6, you might want to take at what I wrote - the answer is there.

[Davidk]

Re: Filtering a Parent List Based on Values in a Child List

It's a case where Alpha makes a change to the builder... but it screws over existing working components. Ask Alpha to either undo the change... or make it optional... or make it work like it should. Alpha probably made the change based on a user complaint... didn't test it well... and didn't think it through... figuring if something was wrong... another user will let them know. That's pretty much modus operandi.

The odd thing is that if you build that component under 4770 (which allows the GROUP BY in this scenario) and then run that component under a pre-release build... it'll work. There's nothing in code that will complain... because... as you said... you are working with... and have the PrimaryKey.

It's the builder that is messing things up.

I'd complain loud and clear to Alpha on this one. I'm shaking my head.

[Pizzadude]

Re: Filtering a Parent List Based on Values in a Child List

Alpha probably made the change based on a user complaint...

Ironically, this change was introduced as a result of my own bug report related to preventing navigation in a parent list if a child list is dirty.

I have been exchanging emails with Selwyn about GROUP BY, and he is taking the position that the restriction is correct and will remain in place.
He said "if you create a list that is based on a SQL query that uses GROUP BY then having a detail view does not make sense since the List is not updateable and the detail view is meant for updateable lists."

The same restriction applies to DISTINCT. I believe there will be other complaints about this.

In the most recent message from Selwyn he said "I just need to know if you think there is a bug and if so, what it is so I can fix it." So he is willing to listen. I need to put together an iron-clad argument as to why the restriction goes too far, and what needs to be done to fix it. It would go something like this:


A list control with a detail view must return the Primary Key of the primary table in the control, and the list is not allowed to be multi-select.
The detail view is already keyed to the Primary Key of the primary table in the list control, so the detail view sync method - updateListFromUXControls() can only update one record, and it will be the correct record.
The use of GROUP BY and DISTINCT do nothing to interfere with the detail view updating the selected row in the list in this situation.


Are there any scenarios where the above statement it not true?

And what would be the best fix? I can't think of a situation where the restrictions are necessary.



Input from anyone would be appreciated, including anyone at Alpha.

[Pizzadude]

Re: Filtering a Parent List Based on Values in a Child List

Here is an update...

In my child list - named Stops - I already had a column - StopOrder, and in each row it is a sequentially numbered value: 1, 2, 3 etc.

The first stop is always a Pickup and StopOrder = 1, the second stop StopOrder = 2, and there are always at least two stops. All this is to explain that I have a unique value (StopOrder) in every stop, and there is a logical pattern.

In the parent loads table I added two new columns: StopOrderJoinPU (default value = 1) and StopOrderJoinDEL (default value = 2).

In the parent list - named Loads - I modified the join to the stop table:

INNER JOIN stops stops ON  loads.Load_ID = stops.Loads_FK  AND ( loads.StopOrderJoinPU = stops.StopOrder  OR  loads.StopOrderJoinDEL = stops.StopOrder )

The parent list will now only return two rows for each load, no matter how many stops there are in the stops table, because it will only match records where stops.StopOrder = 1 or stops.StopOrder = 2.

The next step was to make sure the initial filter and any additional filters I apply to the Loads list includes either "stops.StopOrder = 1" or "stops.StopOrder = 2", but never both.

In addition, because I have a search part that allows users to specify values in multiple controls in order to perform custom filtering of the parent Loads list, I added an unbound hidden control SEARCH_StopOrder with a value that is automatically set to either 1 or 2, depending on the search criteria selected.

Now the parent Loads list always returns only one row per load no matter how many matching stops exist.


This allows me to have columns from the stops table available in the parent Loads list, which allows me to filter the parent list based on values in the child list (see the title of this thread).

It also allows the parent list to have a detail view, while still being updatable.


I also spent some time working with test components, with the parent list returning multiple rows, having a detail view, and being updatable. I could not get it to produce an error or misbehave in any way. The detail view will always update the selected row, using the selected row's Primary Key. So the GROUP BY restriction does not add any additional protection or data integrity. It is already covered by the restriction that having a detail view requires the list control to return the Primary Key. The GROUP BY restriction is overkill.

This will affect any application that uses List controls that have a one-to-many relationship to another List control. It is only a problem if the parent list has a detail view and needs to be updatable. I wonder how many applications will be affected?

[Davidk]

Re: Filtering a Parent List Based on Values in a Child List

I feel the main question is... are you asking Alpha to resolve this? Have you submitted a case whereby the GROUP BY / DISTINCT scenario is getting in the way of a process that should be able to run?

At the very least an option could easily be added... "Ignore GROUB BY / DISTINCT restriction". Alpha is already checking for the condition... now allow it to be ignored.