Form Will Not Display Data

[ikopanas]

I have a form with 4 subforms (not connected) each one displaying a query …

When one of the subforms wont return any record none of them shows any results…!

My OS is Windows 10, LO is 6.3.4.2 (64bit)
the dependecies on the form could be seen

and the query that runs and causes the problem is this one:

SELECT “Customer”.“Name”, “Service”.“Description”, “Editor”.“Editor Name”, “Order”.“Date”, “Order”.“Price” * “Order”.“Service Quantity” AS “Price”, “Order”.“Service Quantity” AS “Services”, “Order”.“Finished Jobs” AS “Done”, “Order”.“Editor Fee” FROM “Order” LEFT JOIN “Customer” ON “Order”.“Customer ID” = “Customer”.“Customer ID” LEFT JOIN “Service” ON “Order”.“Service ID” = “Service”.“Service ID” LEFT JOIN “Editor” ON “Order”.“Editor ID” = “Editor”.“Editor ID” WHERE “Done” < “Services”

and here is a sample:
Removed by @Ratslinger

[Ratslinger]

Hello,

Please when posting include your OS, specific LO version and with Base the database used (and connector if applicable).

On the surface, the ways this may happen is either the sub forms are related in some way to the main form or the queries are related. It is not clear as to the 4 sub forms as there are only the four table controls on the form. At least one should be a main form. The reality is that if these are displaying individual queries they could each be on their own main form. Form in this discussion deals with the internals and not the opened singular form from the Base main screen. There can be multiple internal main forms.

If further problems or no resolution please post a copy of the Base file (even if you are using an external DB). Edit your question to add this file. Please do not use an answer.

[Ratslinger]

@ikopanas,

Sorry but the additional information added to the question does not help. Without a sample it will be difficult at best to solve this problem. The images do not show the necessary information and the Query could be any query since there is no method without the sample to tie this all together.

Please provide a sample.

[Ratslinger]

Hello,

The problem is your SQL. You are stating a condition which creates no result set - in all records "Service Quantity" is same as "Finished Jobs"

You SQL asks for:

WHERE "Done" < "Services"

and earlier in the statement these come from:

   "Order"."Service Quantity" AS "Services",
   "Order"."Finished Jobs" AS "Done",

SQL is returning what you asked for.

Edit 2020-03-10:

Once I had seen no records for the main form was being returned, had not looked at the further at the sub forms. There is no reason to have sub forms if they are not connected to the main form. You can have many internal main forms on a form.

It appears there is a bug here. If the SQL attached to the main form is modified to return a result of even one record, then the data is displayed in all the forms. Regardless of the SQL and what it returns, if the existing sub forms are changed to main forms (just a simple drag & drop process) then all works fine.

Again, there is no reason to have a sub form if there is no connection/relation to its parent. Do not understand why the main form AND a sub form attached to it would have the same data (qOrders4EditorsFiltered) attached to it.

[Ratslinger]

@ikopanas,

In future please post scrubbed file - no personal/confidential information.

[ikopanas]

You are right about the file posted… Thanks

The question was why I get empty results in the other 3 queries where there is not a
WHERE “Done” < “Services” condition…

[ikopanas]

Thank you! Pretty helpfull! Keep on the good work!!