DataView Query Problem

How do I?
1

My DataView is based on this query:

SELECT
  `BoardReports`.`BoardReportsKey` AS `BoardReportsKey`,
  `BoardReports`.`BoardPositionKey` AS `BoardPositionKey`,
  `BoardReports`.`ReportKey` AS `ReportKey`,
  `BoardPositions`.`PositionName` AS `PositionName`,
  `Reports`.`ReportTitle` AS `ReportTitle`
FROM
  `BoardReports`
  INNER JOIN `BoardPositions` ON (
    `BoardReports`.`BoardPositionKey` = `BoardPositions`.`BoardPositionsKey`
  )
  INNER JOIN `Reports` ON (
    `BoardReports`.`ReportKey` = `Reports`.`ReportsKey`
  )
WHERE `BoardPositions`.`BoardPositionsKey` = '0b1dba54-39e9-4e48-9447-768f2af111e4'
-- WHERE `BoardPositionsKey` = ?

It works fine so far. when run as above in the query designer it returns the proper record. to run in production, I remove the comment mark on the last line and insert comment mark on the other WHERE, then save the query. So far so good.

What I really want is to show ALL reports in the DataView, not just the ones that are in the join table (BoardReports).

I tried the following query:

SELECT
  br.`BoardReportsKey` AS `BoardReportsKey`,
  br.`BoardPositionKey` AS `BoardPositionKey`,
  r.`ReportsKey` AS `ReportsKey`,
  r.`ReportTitle` AS `ReportTitle`
FROM `Reports` AS r
  LEFT JOIN `BoardReports` AS br 
    ON  r.`ReportsKey` = br.`ReportKey`
    -- AND br.`BoardPositionKey` = ?
    AND br.`BoardPositionKey` = '0b1dba54-39e9-4e48-9447-768f2af111e4'
ORDER BY `ReportTitle`;

This works perfectly in design-time, and I can run the query. it properly returns all reports, and the one that is in the join table for this BoardPosition shows pertinent values from the join table.

Unfortunately, when I reverse the comments on the AND clauses and save the query, at run-time I get an error “Can’t parse query”

Perhaps the query parser can’t handle parameters in a join? I’ve verified that the parameter has the proper value at run-time.

I want the dataview to show all reports, with the ones that are selected (in the join table) indicated by other values being visible on those lines. I will take care of that part, but I need to get the query/dataview working.

Can you tell me if what I want is even possible? If I can’t show ALL reports in the dataview, I can work around that by having an “Add Report” action button which opens up another form to add a record to the join table. But I’d rather not do that if not needed. I already have code to swap the record in and out of the join table when the dataview item is clicked, but I need the dataview to be able to show the items.

I used ChatGPT to try and solve this, but it doesn’t have specialized knowledge of how Five works, and can’t solve the issue. Can you folks help me?

Thanks…

2

I changed the query considerably. Here is the current version:

SELECT
    r.ReportsKey,
    r.ReportTitle,
    br.BoardReportsKey,
    br.BoardPositionKey
FROM Reports AS r
LEFT JOIN (
    SELECT *
    FROM BoardReports
    WHERE BoardPositionKey = ?
    -- WHERE `BoardReports`.`BoardPositionKey` = '0b1dba54-39e9-4e48-9447-768f2af111e4'
) AS br
    ON r.ReportsKey = br.ReportKey
ORDER BY r.ReportTitle;

When I run it in the query designer, I must comment out the first WHERE clause and un-comment the second one. Then when I run it, it returns the expected data. Unfortunately, when I restore the comments so the parameter is used, I get “Unable to parse SQL” when I run it in the actual application.

This version doesn’t try to use a parameter in a join. But it does use a parameter in the subquery. What is going wrong here?

3

Hi Ron,

Could you please share a new version of your app that contains this query, and let me know the name of this query and where it has been used (Form/page/Action)?

Thank you

Regards,
Elton S

4

There is a new version of the FDF on OneDrive. Thanks…

5

Sorry, forgot to mention the name. it is called dvqBoardReports. It is the query that powers the DataView called dvBoardReports. This DataView is used on a new page in the BoardPositions form. There is a Reports page, which is a join page. I don’t think that will work for me, because i need to do other things when I select or de-select a report on the Join page. So I created an Action page called Reports2 for now, and it is powered by the above-mentioned DataView.

6

If you can tell me why the query won’t run at actual run-time, and we can fix the problem, this will help considerably.

If you tell me that this will never work, I can regroup and do the data view a different way. for example, in my StudyGroups form, I have a Members page powered by a DataView. It only shows the members that are actually there, but there is an action button on the form which brings up a different form to add a new member. I can do this in the form I’m trying to get working, but it is more complex than simply showing all reports in that DataView. Hope you can help…

7

Hi Ron,

The query has a code comment using the comment commands “/*“, and “*|”. And this is causing the issue.

Could you please replace them with a double dash “- -”, and it should work.

Meanwhile, I will investigate this scenario with our team to understand why this comment commands did not work.

Also, make sure you are mapping all the queries’ fields in the Data view; otherwise, they won’t show in the application.

Thank you,
Regards,
Elton S

8

Hi Ron,

Actually, you don’t need to change comment commands. Could you please remove the ‘;‘ from the line: ORDER BY `Reports`.`ReportTitle`

It should work after this change. Please let me know how it goes.

Regards,
Elton S

9

Thanks Elton. Removing the ; worked. Don’t know why it worked. Even with the ; it worked in the query design window. Seems live query parsing at run-time is different.