Question on messaging by Christian Fries, on 13/9/20:
Hi, trying Corteza, I have some questions / unexpected behaviour:
when I use the field-type “record” in a module, the search field in a record list page (showing records of that module) does not search for the records (record names) as if they were text strings. So using records disables searching for those records.
when I edit such a record (settings), I can chose field where the query searches in. But that only works within the settings modal dialog. I expected that this also works with the search field in the record list page.
Can I somehow do complicated SQL code to do inner joins of tables (modules)? Maybe in the Pre-Filter field? Can I use data from other modules in the Pre-Filter field (simple WHERE… SQL statements?)
Can I use a record-type as a variable as in object oriented programming or as in SQL to get its fields like contact.lastName or contact.telephoneNumber?
Yes, that’s correct. The search box in record list currently does not search on the names of related records. However, you could make a workaround with a short automation script and 1 extra field. Let me give you an example: if you have a list of contacts and each contact is part of an account, you cannot search for the account name in a list of contacts. So, you can add a extra string field to the Contact module, names, for example, “accountName”. And then, with an automation script that triggers after oncreate and onupdate you fill this accountName field in the Contact module with the name of the account, getting it from the account module. We did something similar here: corteza-ext/SetLabel.js at develop · cortezaproject/corteza-ext · GitHub.
The fields you select where the query searches in is used in the record fields when you are viewing a single records. So, if you view a contact record, you have an “Account” record field. When you edit the contact record, this “Account” field is a select, and if you type here, Corteza searches for account records, using the fields you selected in the settings
Can you give an example of a query you would need? At the moment we allow simple SQL statements and things like: AccountId = ${record.values.AccountId}
Am I correct you refer to what I pointed out in my answer above?
Example of SQL query I would need (or maybe it is possible to accomplish via some other feature of Corteza):
Let’s say (I’m making this up) I’m are a car manufacturer and want to build a front end editor for my car production database with Corteza.
A car has lots of parts, e.g. windows, like a front window, a left window, a rear and a right window and for each of those parts we want to have tables / modules to store their individual properties.
For simplicity, now only consider a left window.
When a car is produced, the car is added to the carTable, which has a column carID (1,2,3,4…) and a column leftWindowID (17,18,19,…). This way I know that window nr. 17 was used in car nr. 1, window 18 in car 2 and window 19 in car 3.
In the leftWindowTable, windows have the leftWindowID(17,18,19,…) and a column “glassMaterial”, where we store that window nr. 17 is made from “MaterialA” and window 18 and 19 are made from “MaterialB”.
Task: find all cars where MaterialB was used for the leftWindow. Result: car nr. 2 and 3.
In a MySQL admin tool like phpmyadmin, I would define a procedure called “findCarsWithMaterial” with a variable called glassMaterial, so on running the procedure the user can type in “MaterialB” for the glassMaterial variable. In Corteza I would like this to be a text input or a search field.
MySQL query:
SELECT car.* FROM carTable car INNER JOIN leftWindowTable w //combine carTable with leftWindowTable
ON car.leftWindowID = w.leftWindowID //in the meaningful manner so we know which leftWindowID was used in which car
WHERE w.glassMaterial = glassMaterial //but show only those entries, where the leftWindow’s glassMaterial is “MaterialB” (user input was glassMaterial=“MaterialB”)
This results in a list of car 2 and car 3 from the carsTable.
Maybe I can use your answer 3. for this…