Subform Master & Slave Not Working

[PhLo]

Every time I have used the links between Master and Slave fields to link a subform to a main form, it has worked… until now. In this example, the master and slave links have absolutely no effect at all.

The scenario is that I have an inventory (aka “item”) table where each row represents an item in inventory. There is a subform underneath the item form, and the two are linked by the common field “itemid.” In the item table, the field is just called “id,” but this doesn’t matter.

The expected behavior is that only the line(s) that share a common Item ID master/slave relationship will appear in the table control. However, as I scroll through records of the Main Item form, nothing at all happens to the subform with the table control inside. It shows every single possibility even if the item id doesn’t match between the two forms.

You might think from my image that I didn’t include the Item ID in the subform because it doesn’t show as a column. I assure you it is there. To verify, I reveal the exact SQL statement used as a source of the subform, along with the visual output of testing/running the SQL in the SQL editor. Clearly the Item ID is among the columns. Not only that, but for thorough testing, I actually added the Item ID as a column in the subform table control, but then hid that column because it isn’t needed visually.

The filter master/slave connection does nothing at all. You can see that all three possibilities show up no matter what, even though their Item IDs don’t match the main form.

Is it because the SQL of the subform is quite complex instead of just a table call? It needs to be complex because it selects all invoice lines, summing up the totals grouped by month. This effectively gives me monthly usage information - quantity and total price. There isn’t much data yet because I’m just testing. There are only 3 unique items entered on one invoice, thus the 3 entries for November 2016.

It won’t filter for some reason. Any ideas why?

Visual on how the forms are related, all 3 items show up. In this case none should show up because there are no Item ID # 11 among the three rows. Rather, the 3 Item IDs returned are 5, 8 & 12:

frmMonthlyUsage is the subform which contains the table control. Here are screenshots of the linked fields.

Here is frmMonthlyUsage’s source SQL and how it looks when I test/run the SQL… revealing that the Item IDs are part of the returned set, and this column is in the table control too, just hidden:

The reason I used that particular SQL statement is because I have a MySQL backend. If I try TO_CHAR from HSQLDB instead of DATE_FORMAT, it throws an error. Turning the SQL Native mode on and writing the query in MySQL syntax is the only way to get it to return the results, and it works like a charm… but won’t filter on the subform!!!

[EasyTrieve]

One technique I sometimes use when stumped is as follows: a) make a copy of your project, e.g. if your project is named x, then call the copy x_test. b) start to delete things to simplify it to just the feature that is not working. Test as you go. When you get to the point that it’s super simple and still not working, either you’ll see your mistake, or misunderstanding, or you can submit it here, because it will be simple enough to ask a good question with. I call it divide and conquer.

[PhLo]

Thanks @EasyTrieve. I’ll try something like that tomorrow. At the moment I’m too tired to delete anything except my consciousness. I agree though. It is nearly almost always something I’m missing in my own code rather than a true program bug. So far this one looks like it should be working based on the “Run” or “Test” of the code, which reveals the “itemid”… but I’m sure I missed something. Unless there is some logical limit to the queries… IF TOO MANY JOINS, SUMS AND GROUPS… explode!

[PhLo]

It’s weird that there’s no error message. Everything works, the data looks great… just doesn’t perform the filter on the subtable. Blah blah blah. Sleeeeeeeeeep. zzzz

[Hrbrgr]

What Field-typ(s) your fields “id” and “itemid” have? In The table with “itemid” you have separate “id”?

[PhLo]

Good question. Thought that might be it… but no. Both columns are INT(11) in MySQL. In Base, they are both Numerical controls with zero decimal accuracy. A perfect match on both back-end and front-end. I often call the primary key of a table “id”, but when referenced as a foreign key in another table, it gains the name of the source table for clarity, so “item” (source table) → “id” (source column) becomes “itemid” (foreign key) within the “invoiceline” table.

[EasyTrieve]

Here’s another idea for you: Open up these tables w/ HeidiSQL and carefully inspect their structure. Check things like indexes, default values, esp of key fields, and foreign keys. Also create brand new tables and test your form with them.

[PhLo]

Thanks, @EasyTrieve. Since I solved the problem, I’ll probably not go that distance, but I appreciate the suggestion.

[Hrbrgr]

@PhLo The ID designation and the field types all seem OK. Can you please tell us what your solution is, or your problem was. Thank you.

[PhLo]

It was either a random buggy thing relating to MySQL vs HSQLDB syntax compatibility or perhaps that I named the column month, which is a reserved MySQL keyword. But since Base automatically adds angled quotes to all column names, that shouldn’t matter. Ultimately it was fixed by changing from DATE_FORMAT to a concatenation of MONTHNAME and YEAR functions. Since the functions are common to both SQL syntaxes, I was able to run it successfully in the native Base SQL interpreter.

[PhLo]

I figured out a solution. Apparently in some (or all?) cases, Base has inconsistent behavior between the SQL modes. “Run SQL command directly” in the SQL command window also known as “Analyze SQL command” set to Yes/No in the form dialog breaks the link master/slave fields functionality in certain cases. For simplicity, I’ll refer to this as “direct SQL mode” since Base confusingly uses multiple terms/phrases in different places to refer to the same thing.

The only way to get the subform master/slave link to behave properly is to NOT use “direct SQL mode” but rather let Base interpret/analyze the SQL, which uses its flavor of HSQLDB rather than MySQL.

This is unfortunate for my particular query because I was trying to use MySQL’s DATE_FORMAT or HSQLDB’s TO_CHAR. They are identical in purpose, but you can’t use TO_CHAR if connecting to MySQL as a backend, and if you use DATE_FORMAT in “direct SQL mode” (which is what I was doing), it breaks the master/slave functionality.

Therefore, my solution was to change my query to use functions that are common to both MySQL and HSQLDB and turn “direct SQL mode” off.

Instead of

DATE_FORMAT(`i`.`dateinvoiced`, '%Y - %M') AS `month`

I am now using

CONCAT( YEAR( `i`.`dateinvoiced` ), ' - ', MONTHNAME( `i`.`dateinvoiced` ) ) AS `yearmonth`

Since both MySQL and HSQLDB support CONCAT, YEAR and MONTHNAME, the query works with “direct SQL mode” on or off. Consequently, the master/slave functionality is restored if I run this new query with “direct SQL mode” off, and the appropriate subform records only show up where there is a common itemid. Yay!

To me this feels like a “bug,” but regardless I’m glad to fix the problem and move on to the next problem.

On a side note, Base doesn’t truly pass interpretation of SQL to the back-end database in “direct SQL mode.” Even in direct mode, Base interferes as the middle-man somehow. There are queries that are legal, useful and which successfully return a set in MySQL, but Base cannot even in “direct SQL mode.” Therefore it is a false “direct.” Base must internally limit which queries can be sent or received. I have encountered a number of tricky MySQL queries that don’t work in Base… like string literals to bring in rows that don’t exist anywhere, such as (SELECT 'John' AS firstname, 'Smith' AS lastname) UNION (SELECT 'Jane' AS firstname, 'Doe' AS lastname). "WHERE 1" or "WHERE 1=1" (return all results) also don’t work, though they work fine in MySQL. In addition, MySQL doesn’t require the angled single quote around column or table names, whereas Base interferes by adding them even in “direct SQL mode.” The only reason to use the angled single quotes is if you want to use a table or column name that is a reserved MySQL keyword.

[Ratslinger]

@PhLo You should file a bug report for the link problem on Bugzilla.

As for the SQL, ‘quite complex’ and ‘tricky’ are a bit much as this is pretty standard stuff. For example WHERE 1=1 works not only for MySQL but others including the default HSQLDB. WHERE 1 works for MySQL (personally find no use for these).

[Ratslinger]

and:

SELECT 'John' AS firstname, 'Smith' AS lastname
 UNION
SELECT 'Jane' AS firstname, 'Doe' AS lastname

works for MySQL (drop parentheses). HSQL needs a table name which makes it more difficult to deal with.

Can’t say I’ve run across anything which doesn’t work as you say. Doesn’t mean there isn’t something as problems crop up as we look for solutions. You found a problem above.

Note - I do vaguely recall using Where 1=1 (or 0) once or twice - possibly in the forum.

[PhLo]

WHERE 1=1 is “tricky” because most people consider it pointless. Imagine a scenario where you call a function or API that expects a WHERE clause, but you want all records. Passing two equal terms for column and value could help out. Mine was such a weird scenario that maybe it’s not repeatable. To get it going all I changed was to use date functions common to MySQL & HSQLDB. Developers would probably just get annoyed reading something so obscure. Don’t want to send someone on a wild goose chase.

[PhLo]

The other problem with this “bug” (if it even is one) is that I can’t provide the developers with a sample file because it is connecting to my particular installation of MySQL. It might not be repeatable for anyone else. Maybe even I can’t reproduce it on my system! I’m happy to help the community, but I feel this is too odd a scenario to be of benefit in reporting… unless you think otherwise.

[PhLo]

Ok, this is lame. I just opened my file that was previously working earlier today, and now it is not working AGAIN, even with the changes I described as a “fix.” I literally changed nothing from earlier. Fun times. I don’t even know what to say about that. For now I’m going to move on to another form because I can’t afford to spend weeks on this particular problem. :S

[PhLo]

AHA… it somehow got changed back to Analyze SQL = No (though I don’t remember doing that). If I change to Yes again (equivalent to what I’ve been calling “direct SQL mode” OFF), then it works. So it’s definitely something to do with the modes. MySQL direct mode, even though its returned results are identical, won’t trigger the master/slave link in my case. Strange.

[Ratslinger]

File the bug report. The developers will be able to cope. It is repeatable.

Still not ‘tricky’.