I have an embedded database in Libre Office. I am building a form in it, and got into a problem.
I have a table called "Author" and another table called "Publication", connected by a third table "AuthorsAndPublications" (it is an m:n relationship). I want to create a form based around the table "Publication", but it should show all the authors of the current publication in a non-editable listbox.
As the datasource for the listbox, I wrote the SQL query
SELECT "Author"."Surname" FROM "Author", "AuthorsAndPublications" WHERE "AuthorsAndPublications"."FK_Author" = "Author"."ID" AND "AuthorsAndPublications"."FK_Publication" = :publicationid
Now I want the parameter publicationid to automatically have the value of the field txtPublicationID which is located on the same form as my listbox. So, when the form is open and shows the publication with ID 1, the listbox should show the authors of publication 1, then when I navigate to the next publication, the authors should be shown the authors of the next publication.
After searching around for documentation and examples, I couldn't find a good description of the Libre Office object model. I did not find an example addressing my case, but after seeing similar ones, the best I could come up with was:
SELECT "Author"."Surname" FROM "Author", "AuthorsAndPublications" WHERE "AuthorsAndPublications"."FK_Author" = "Author"."ID" AND "AuthorsAndPublications"."FK_Publication" = [ThisDatabaseDocument.FormDocuments.GetByName("PublicationForm").GetByName("txtPublicationID").currentvalue]
But this query does not work, I only get the error "Syntax error in SQL expression".
How do I properly reference the value of the textbox within the datasource for the listbox?
