[Solved] How to populate a read-only textbox in a grid with data from subforms in Base

[vaayda]

Hi,

I’m trying to create a database with LibreOffice Base 5.1.4.2 for a research project in linguistics where I would need to catalog different inflectional forms of words (such as “go, went, gone, going, goes” for the word “go”) and classify them according to their grammatical properties, e.g. marking “went” as “past tense” and “goes” as “3rd person singular present”. As words of different parts of speech have different properties, e.g. verbs are marked for tense, mood, voice and person whereas nouns are marked only for gender and number, I have decided to create a table called WordForm, which has a 1:1 relationship to two other tables, NounInflection and VerbInflection, only one of which would be populated for each individual record. The WordForm table is accessed through a grid control (itself a subform of a form called Word grouping together all the forms of an individual word), and the two other tables, NounInflection and VerbInflection are handled as its subforms with individual listboxes for each property, e.g. for form “goes” marking Tense = “present,” Mood = “indicative,” Voice = “active,” Person = “3sg.”

As I am inputting the data, I would like to be able to have a summary view of all the records in the WordForm grid together with the name of the grammatical form in the column “Inflection”, which would be derived from the properties of either NounInflection or VerbInflection. I’m thinking of a concatenated SQL string, which for verbs would look something like this (for nouns I would add a CASE clause followed by the appropriate properties):

SELECT "Tense"."Tense"||' '||"Mood"."Mood"||' '||"Voice"."Voice"||' '||"Person"."Person" FROM "Tense", "Mood", "Voice", "Person", "VerbInflection", "WordForm" WHERE "Tense"."ID" = "VerbInflection"."Tense_ID" AND "Mood"."ID" = "VerbInflection"."Mood_ID" AND "Voice"."ID" = "VerbInflection"."Voice_ID" AND "Person"."ID" = "VerbInflection"."Person_ID" AND "VerbInflection"."ID" = "WordForm"."ID"

Now the problem is that I can’t see any way I can include this query in the main grid control. If I define the column “Inflection” as a textbox, its properties don’t give me the option of populating it with an SQL clause. I don’t think a listbox or a combobox would be appropriate either, since I’m not planning on changing any of the values through the grid control but just displaying them based on the input provided in the subforms. Besides, using a listbox would be very impractical as the number of unique combinations of properties in the language I’m studying could easily go to hundreds or even thousands for any individual word.

Any suggestions on how to proceed? See the following Dropbox link for an idea of what the database could look like:

SubformsFeedingMain.odb

[vaayda]

I think I found a workaround which is probably not ideal but seems to do the job. I changed the column “Inflection” to a read-only listbox by selecting Enabled:No and Read-only:Yes in the listbox properties. I also modified my query clause by adding a column for “VerbInflection”.“ID”, which I then used as the bound field of the listbox and marked WordForm’s “ID” as the data field. It feels counter-intuitive as I’m not really even attempting to change the ID at any stage but it seems to produce the results I want.

For existing records, the grid now shows what I expect. Whenever I create a new record or change the values of an existing one, I need to force Base to notice the change by clicking on the WordForm grid and then clicking on Refresh in the toolbar at the bottom of the page. I guess this could even be automated with some sort of macro.

If anyone has any better ideas or sees a potential problem with this solution, please let me know. Otherwise, I will soon mark the question as solved.

Edit:

After a lot of trying, I finally found the appropriate way to refresh the WordForm table with a macro:

   oMainForm = thisComponent.Drawpage.Forms.GetByName("MainForm").GetByName("WordForm")
   iRow = oMainForm.getrow
   oMainForm.unload
   oMainForm.load 'jumps to the first record
   oMainForm.absolute(iRow) 'moves back to the record which was previously selected

The logical alternative of just writing oMainForm.reload, which was suggested in some other posts and other forums, had no effect on the main table.