To me this feels like a “bug,” but regardless I’m glad to fix the problem and move on to the next problem. 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. Since both MySQL and HSQLDB support CONCAT, YEAR and MONTHNAME, the query works with “direct SQL mode” on or off. I am now using CONCAT( YEAR( `i`.`dateinvoiced` ), ' - ', MONTHNAME( `i`.`dateinvoiced` ) ) AS `yearmonth` Instead of DATE_FORMAT(`i`.`dateinvoiced`, '%Y - %M') AS `month` 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. 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. This is unfortunate for my particular query because I was trying to use MySQL’s DATE_FORMAT or HSQLDB’s TO_CHAR. 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. 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. “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. Apparently in some (or all?) cases, Base has inconsistent behavior between the SQL modes. Rather, the 3 Item IDs returned are 5, 8 & 12: In this case none should show up because there are no Item ID # 11 among the three rows. Visual on how the forms are related, all 3 items show up. There are only 3 unique items entered on one invoice, thus the 3 entries for November 2016. There isn’t much data yet because I’m just testing. This effectively gives me monthly usage information - quantity and total price. 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. You can see that all three possibilities show up no matter what, even though their Item IDs don’t match the main form. The filter master/slave connection does nothing at all. 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. Clearly the Item ID is among the columns. 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. 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. It shows every single possibility even if the item id doesn’t match between the two forms. However, as I scroll through records of the Main Item form, nothing at all happens to the subform with the table control inside. The expected behavior is that only the line(s) that share a common Item ID master/slave relationship will appear in the table control. 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 scenario is that I have an inventory (aka “item”) table where each row represents an item in inventory. In this example, the master and slave links have absolutely no effect at all. 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.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |