So I have a table of contacts, and a table of events. I want to have a join where when on the contact layout I see ALL events, and can add new ones.
What's the easiest way to do this?
I tried joining the two primary keys using ≠ and "allow creation of new" events was greyed out. So I made two fields called "Number1ForJoins" in both tables, which were calculations with the number 1 in them.
I tried two fields that are blank "number" fields and joined them.
But when I try to add a new event it says "This field is not editable"... which is odd because it's "editable" but I can't "add" new events.
Is there a standard way to say "show everything from this otherwise unrelated table in a portal"?
A cartesian join returns all records. It is not based on a relationship, therefore you can't make a related record via the relationship. You will need to script the new record creation.
Ok yeah that's not what I'm looking for then. I created a separate scripts for now but I might try some other joins to get it to work showing all and also allowing creation of new records within the portal, which is the ideal situation.
I think there is a fundamental misunderstanding here.
A relationship works by matching one or multiple fields of left side records of the relationship with an equal amount of fields of the right side records of the relationship, each according to an operand (=, ≠, <, ≤, >, ≥, x).
FileMaker can create records via a relationship when all operands are =, ≤ or ≥ because it copies the value of the match field(s) of the record that already exists into the match fields of the record being created.
That can't happen when any operand is ≠, <, > or x in the relationship definition. These operands allow for a range of matching values, none of which is a left side value. For example, let's say any right side record matches a left side record if the right side quantity is greater (>) than the left side quantity. A left side quantity of 1 would match a right side quantity of 2, 3, 4 and any other value greater than 1. What quantity value should be set when creating a right-side record via the relationship? There is an infinite number of possible values (short of technical limitations).
In short, you will be unable to show all records via a relationship and be able to create a record via the same relationship.
This is technically possible if you have an auto-enter calc on either side of the relationship that will always match. E.g. auto-enter a value of "1".
I wouldn't necessarily recommend this, and would probably favor an x join for displaying all related and a separate field with a script to create new related records. My hunch is that it might have better performance (but that's just a hunch!)
EDIT: I should mention explicitly: DO NOT ENABLE 'delete related' IN THE RELATIONSHIP. It will delete all the records in the other table if you delete any single record in the current table.
I tried joining a blank field to a blank field with no luck.
I was going to try a calc of the number 1 in parent joined to a regular number field in the child.
But in this case I already coded it separately and this feature is only for one tech savvy user so I’ll prob just leave it as a card.
It’s a little more cumbersome for the end user and it works differently than other portals in the app, and I usually go by making it easier for end users when possible since I don’t think the speed difference will be huge but since it already works I’ll probably leave it. Next time I’ll join 1 to 1.
PS, your idea here would also work if it's a one-way creation street.
I was going to try a calc of the number 1 in parent joined to a regular number field in the child.
I'd still add an auto-enter to the child in case you ever create child records another way, but, if it's always done via portal, even the auto-enter would technically be optional with your approach.
