List with one to many relation on a UX

[acceptthis]

I'm trying to create a list that has a one to many relationship with a field on a form. I'm at a loss. What don't I understand?

"EstimateID" is the linking field. I created an argument that gets the number from the EstimateID field (see image below)
2023-01-16.png

I then use the argument in the filter

2023-01-16 (1).jpg

The list doesn't get filtered. The value of CURRENT_ESTIMATE returns the number 0, which I know because I put it in a textbox so I could see the value

Can anyone direct me so I can get a better understanding of how it's done?

[Lance Gurd]

In your argument definition you have CURRENT_ESTIMATE pointing to the {Estimate} field, but in your description above you say it should be getting it from the {EstimateID} field. These two fields look like they could be different?

[acceptthis]

They are the same. I changed the name of the UX field to match now. It was estimate, and now it is estimateID to match the name of the field in the database.
2023-01-16 (2).png

[acceptthis]

I guess the question is, am I doing and thinking correctly? Is that how I'm supposed to get the list filtered by a field on the UX?

If so, maybe there's something going on in my SQL because I'm constantly getting 0 for my CURRENT_ESTIMATE argument. If I make the argument variable a character, it comes back blank.

[madtowng]

Just makes you wonder if it's as easy to filter a viewbox based on a ux control.

[Lance Gurd]

That is easy Gregg but i think the issue here is the argument is not getting the value of a field for some reason

[peteconway]

Make certain the field in the SQL is the same type as the type in the Argument. If you have a numeric argument the fields nee to me numeric, as dos the control type you are baaing the argument on.
Pete

[madtowng]

That's what I get for starting at the end. My guess is the list control is executing the query before the value gets into the control.
Maybe the trick is to have an on-change event for that control assign the value to the argument, then reload the list control.

[acceptthis]

Thanks all. At least I know I'm doing it the way I'm supposed to. I'll check everything and get back.

[acceptthis]

I checked all the fields and still have the same problem. I think you guys are correct when you say that it's not passing the arguments to the sql statement. I traced the sql and noticed that it runs 3 times. I don't understand the trace data, so maybe it's normal, but these are the three sql commands-- (ellipses added to lessen code)--

----------- Portable SQL to be translated to native syntax ----------
               SELECT FIRST 1 … WHERE estimates.EstimateID = :PrimaryKey_1
                --------- END Portable SQL to be translated to native syntax --------
                ----------- Native SQL with named arguments ----------
               SELECT  …  WHERE estimates.EstimateID = :PrimaryKey_1     LIMIT 1
                --------- END Native SQL with named arguments --------
               ----------- Native SQL passed to driver ---------
              SELECT …  WHERE estimates.EstimateID = ?     LIMIT 1
               --------- END Native SQL passed to driver --------
​

On the third select command in Native, the Where statement is a question mark.

I'm done trying to figure it out and will just use a parent list to child list. I have no problems when I do that. I didn't think it would give me such headaches.

thanks again.

[peteconway]

A lot of work to try and help you as we have to guess - so this is my least request show the sql you are emitting. With the select statement in it.

[Lance Gurd]

Attached is a proof of concept. You may need to change the Northwinds connection string in each of the lists to see this. But it proves that setting an argument to an item and changing that item can result in a list showing the child items.

There is a viewbox on the left of the UX that shows a list of orders from the northwinds database. Select any value in that viewbox list refreshes the viewbox in the centre and the list on the right.

The reason for a viewbox in the centre and a list on the right are to show the speed difference between the two, while these are filtered by the argument value this is not very noticable, but if you remove the filter in the VBList and listlist items you will see the difference, the viewbox being noticeably faster. Hopefully this will help someone

NorthwindsOrders.a5wcmp

[acceptthis]

Pete-
I hope I'm not taking too much of your time. If so, please don't feel obligated.

This is the sql statement in the list builder:

SELECT UnitsInEstimateID, EstimateID, UnitID FROM units_in_estimate  WHERE EstimateID = :CURRENT_ESTIMATE

This is the full detail of the query for the list that I got from the trace window (if that's what you need. If not, I'm sorry. I don't know where else to get it. I'm a novice when it comes to sql):

********************* START CA5SQLConnection::ExecuteQuery *********************
               Thread running as: DESK2\Steinfeldt
                Connection mode for this call is: Portable SQL

               ----------- Portable SQL to be translated to native syntax ----------
                SELECT  count(*)  FROM units_in_estimate  WHERE EstimateID = :CURRENT_ESTIMATE
                --------- END Portable SQL to be translated to native syntax --------
                ----------- Native SQL with named arguments ----------
                SELECT  count(*)  FROM units_in_estimate  WHERE EstimateID = :CURRENT_ESTIMATE
                --------- END Native SQL with named arguments --------
                ----------- Native SQL passed to driver ----------
               SELECT  count(*)  FROM units_in_estimate  WHERE EstimateID = ?
                --------- END Native SQL passed to driver --------
                -------- Arguments -------
                [1] current_estimate (Input) = 1
                --------- Bind Map -------

              [1] Argument current_estimate [Input] = 1

                ------ Call Result -------
                ============================
               Call Result:
                -----------
                              Syntax:                                 MySQL
                                API:                                        MySQL
                                ResultCode:                       0
                                NativeCode:                       0
                                NativeText:                  
                               Rows Affected: -1
                               Rows Returned: -1
                                Insert Identity: 
                               ----- Formatted Error Message ----

                              Success
               Current Connection Properties:

               Database API = MySQL
                Database Syntax = MySQL
                Trace SQL = Yes
                Trace SQL Format = Text
               Trace SQL Level = Diagnostic
                Trace SQL File = C:\Users\James\AppData\Local\Temp\AlphaAnywhere\p_21536\a5_temp_4f21d015_23e5_4de8_aaf3_136e9188c708.sqllog
                Time zone is UTC (SQLiteOnly) = Yes
                SSL = No
               Trust Server Certificate = No
                Enable Column Encryption = No
                UseServerSidePrepare = No
                Database Version Requested = 8.0
                Database = landscape_estimator
                Port = 3306
               Server = localhost
                UserName = root

                ============================


Elapsed time in milliseconds: 83
*********************  END  CA5SQLConnection::ExecuteQuery  *********************
​

Lance,
Thanks for sharing the file. I've never used view boxes, so I'm going to test some things on it. I have some queries that take some time. it would be nice to see how it speeds up.

-Brad

[madtowng]

Hi Brad,

I avoided list controls and viewboxes for a long time. When I finally gave in, I opted for the viewbox based on conversation
from Lance and Pete. They have both been fountains of information and assistance as I work my way through the frustrations
of something new.

With that said, something I took to heart is loading data up front, then searching the control (in my case the viewbox) for specific data.
If you want to see this in action, feel free to check out my video finder app HERE . The only call to my server is at the beginning , when
it loads the 1349 records I have into the viewbox. After that, all filtering is done client-side.

There are several threads involving me and viewboxes where I work my through as a newbie that has difficulty with text overload.

Feel free to reach out for help (I'm far more proficient with mySQL than viewboxes)

Gregg