Hello, as a “former FileMaker developer” I have liked to use a FileMaker feature, that would fill the primary key on the left side of a relation if a field was filled in the related file.
Let’s put this example:
I have a company that has adresses (1:n). One of these adresses is “standard”; in this case the primary key of that particular address is stored in the company table (1:1).
When creating a new company I could use the relation that points to the standard address although the primary key of that address is not known at the point of data entry. FileMaker then created that record and threw back the new primary key of that address in the company table without any further “programming”.
Servoy only auto-creates records for which the key on the left side of the relation is already there (e.g. pointing from a known company ID to the address table). It is not able to create a record where the left side of the relation is not yet known.
That means that in my example the address has to be created first. But when the user doesn’t enter any address data, that record has to be deleted by the developer. It’d be easier to just create the record if fields are filled.
Do you think it is possible to let Servoy manage this?
I’m a little confused - since FileMaker, like Servoy - REQUIRES a key on the left hand side of a relation in order to make it work.
So - if you have a standard address with the ID of 1, and your companyID is also 1 and you have a relation that matches companyID::addressID - the address will show up (let’s say in a portal). If you create a NEW company record with ID of 2 - then the standard address (with the ID of 1 will NOT show up).
However, if you have a column in your company file with a constant (i.e. I call mine “one” - with an auto-enter value of 1) - and you use companyOne::addressID - then regardless of the ID of the company, the Standard Address (id 1) will always show up in the portal.
what FileMaker can do, is the following: if you allow a relation to create records and the right side of the relation is a serial number, then you can edit a field in the related file even when the relationship is not valid at the time you do that.
FileMaker will then create the related record and throw back the serial number and the proper field on the left side of the relation. This is what I like to be able to do in Servoy.
Maybe some more explanation on the why: Let’s say you have a person table and a table that holds all kinds of phone numbers, email addresses and so forth (“communication”). We have learned in past projects that it is good to have 1:N communication, but on the other side people want to simply export their persons with phone and fax number without having to hassle with a relation. So we made the “standard” communication available in the person table by storing their IDs.
The person table therefore has the ID of the Office phone, Private phone, Email and so on. Let’s say you have 5 or 6 IDs that give you direct access to those standard numbers. All numbers are stored in the communication table, though.
Now the issue: if you create a new person, I want to present the user a form that shows the person’s data, the “standard” address and standard communication. Standard address and standard communication are linked through their IDs (the ID of the communication record is stored in a field id_phone_office in the Person table). I only want to create those records if the user enters data. But the user cannot enter data since the relationship is not valid. This is the point. In FileMaker, when you do that, if there is data, the record is created and the ID is thrown back in the table on the left side. If there is no data entered, FileMaker will not create the record.
Yes I know that. But what if contact_id on the left side is empty?
If contactsid on the right side was a sequence, FileMaker would create a record in contacts and fill contact_id on the left side with the sequence of the new record in actions.
Probably this is a little trickier in Servoy because here we can handle multi key relationships…
patrick:
Probably this is a little trickier in Servoy because here we can handle multi key relationships…
Yep. That’s it in a nutshell.
In general - you’ll want to programatically create a new record through the relation (so the key will be filled) - and then set the record data.
Have you tried to make a relation back the other way (from the child table)? That may work as well (contactsid → contact_id - with “allow created records” checked).
If contactsid on the right side was a sequence,
FileMaker would create a record in contacts and fill contact_id on the left
side with the sequence of the new record in actions.
In Servoy this would go like:
(script in actions form)
controller.newRecord() //action_id and contact_id are auto set (pk, sequence)
forms.contacts.newRecord() //servoy auto “selects” this new record
forms.contacts.contact_id = contact_id //servoy sets contact_id in the newly created and selected contact record.