Managing people related to school using a portal

[Phillip Boushy]

I have the following tables:

[schools]

ID

School Name

...

 

none

=

Allow Creation of Records, Delete Related Records

 

[JOIN People with School]

ID

SchoolID

PersonID

 

Allow Creation of Records, Delete Related Records

=

Allow Creation of Records

 

[People]

ID

First Name

Last Name

...

 

 

I have a layout "School Information" from [schools] as Form

 

Inside the layout I have a Portal that shows related records from [JOIN People with School].

The portal row displays People::First Name, People::Last Name and several other fields. It has the People::ID field hidden.

 

I am able to remove the relationship between the school and person by deleting the row in the portal but when I click in a field in a blank row it does not create a new record.

 

How do I make it where I can:

1) Create a new person, and add the relationship to the school.

2) Add relationship between an existing person and the school.

3) Remove the relationship between school and person, but not delete person

4) Delete the person if this is the only school that this person is related to

 

I would like to set this up in a way that requires as little scripting as possible. I'm hoping that items 1-3 can be done without scripting. I suspect item 4 requires scripting. I have a  script that does 2. I have the start to a script for 4.

 

I can export an empty version of my database if required, but I'm hoping it is not needed.

 

Please let me know if you need any further information.

 

 

[eos]

I am able to remove the relationship between the school and person by deleting the row in the portal but when I click in a field in a blank row it does not create a new record.

 

A new related record is only created if you actually save data in a related field; simply clicking isn't enough (and a good thing, too).

 

Here's sample file that may help you get started on your tasks 1 to 4, but …

 

I would like to set this up in a way that requires as little scripting as possible. I'm hoping that items 1-3 can be done without scripting.

 

… alas, it involves scripts (a few). Good opportunity to get used to them, because they cannot be avoided.

 

(I almost forgot to ask: What does "Script Kid" in your signature mean…?)

 

EDIT: Attached corrected and prettied-up version 002

PeopleAndSchoolsv002_eos.fmp12.zip

[Phillip Boushy]

I like scripting (primarily BASH, Python, and PHP) but I prefer to use built-in methods of doing things if they exist and are functional.

 

I wasn't clear on what I meant by "when I click in a field in a blank row it does not create a new record." I mean when I try to click in the field it doesn't let me, it's like it's rejecting the option to create a new record. Does that make sense? I thought I might accidentally messed up my layers in creating my various fields, buttons, and designs, but I checked all those and they appeared fine. For some reason it just wouldn't let me create the new record. I'll take a look at the example you sent and see what I might've done wrong.

[eos]

I like scripting (primarily BASH, Python, and PHP) but I prefer to use built-in methods of doing things if they exist and are functional.

 

Scripting is an integral part of developing FileMaker solutions, and scripts are very much a “built-in method” of FM. I don't know what makes you think differently. 

 

I mean when I try to click in the field it doesn't let me, it's like it's rejecting the option to create a new record. Does that make sense?

 

There are several reasons why this could fail; but on re-reading your first post, the problem is probably as follows:

 

If you only display fields from the People table in the portal, you cannot use them to create a join record. Fields from the People table will only be valid (accessible) when you have created a join table record that points to a People record – which means you cannot use them to create that join record in the first place …

 

You can check this by creating a dummy field in the join table. Put this into the portal, enter it, put it some text and exit it. You will see that this creates a new join table record – alas, it's only related to the school, not a a person, since you haven't out in person foreign ID … 

 

One approach is to create a value list with people ID and name, and use this value list to format the join table person FK field in the portal as a drop-down or popup menu (where each format has some pros and cons, and you'll usually end up with a hybrid).

 

Or go a different route and present the user with a nicer selection UI that offers better type-ahead capabilities, on-the-fly creation of missing entries etc. – all of which of course involves heavy scripting … 

 

I'll take a look at the example you sent and see what I might've done wrong.

 

The file won't help you in that regard since the relationship doesn't allow creation; related records are created (and deleted) purely by scripts.

 

As you can tell from my description, you can create join records without scripting, and points 2 and 3 also; but #4 would be very difficult …

[Phillip Boushy]

I actually love the design of the "add person" where you can search for a person or if they don't exist, create the person by assuming the first and last name based off the full name. I was trying to figure out how to do that for the "Add Existing Person" button I have, but it makes so much more sense to use the method you do.

I've got the add existing user working. I'm testing add new user. And I need to edit the delete script to match my tables and fields now... I really like this setup, and luckily I don't have many relationships that require this kind of thing.

I agree that scripting is a "built-in" way, maybe a pre-built way is a better description (e.g. button action: delete portal row)

A few questions:

1) I've noticed that you have a table called UI that stores UI elements and then you make buttons by inserting the container field and setting that to do a script (e.g. + button: openPop) is this a recommended way, or just something you do to allow people to custom the interface?

2) I noticed you did #1 and called a script to just open a popover button instead of just placing a popover button. Is there a reason for this aside from UI?

[eos]

I agree that scripting is a "built-in" way, maybe a pre-built way is a better description (e.g. button action: delete portal row)

 

Well, every programming language at its core consists of a built-in set of statements, operators and keywords (and a set of syntactic rules), which in this sense are also pre-built.

 

It just so happens that in FileMaker the statements can only be selected by clicking; that method doesn't make the end result any less a script than if you had written them all manually.

 

It also makes the application more beginner-friendly, since the click-approach prevents a number of syntax errors every beginner inevitably sees when writing their first programs … but of course can hamper a more experienced developer.

 

The other part of scripting happens in the statements that use the calculation engine, where you build expressions to get and set results from your database; this combination gives you almost limitless possibilities out of the box.

 

1) I've noticed that you have a table called UI that stores UI elements and then you make buttons by inserting the container field and setting that to do a script (e.g. + button: openPop) is this a recommended way, or just something you do to allow people to custom the interface?

 

It could be both (AND), couldn't it?   Actually, this is a way to store graphics for universal use; note that the fields are set for global storage, which means you can access and use those fields from any context. Also, this is a typical deployment scenario for repeating fields: to store a group of related icons each.

 

In a production system built for other users, you wouldn't allow users access to that layout (or any places where the schema is defined, for that matter). It allows the developer to customize the interface very conveniently. Don't like the “Add” button icon? – Replace the old one in the global field, and have all layouts show the new icon. There.

 

2) I noticed you did #1 and called a script to just open a popover button instead of just placing a popover button. Is there a reason for this aside from UI?

 

You can turn any object into a button (by assigning it a script (step)), but not into a popover button; and a popover button's appearance is harder to change in its appearance.

 

So I personally take the easy route and use an existing object as a button to open a popover whose button I then don't have to care about. And if you need to do some pre-processing anyway – i.e. use a script to refresh a portal display – then this approach is fast and straightforward.

 

[…] luckily I don't have many relationships that require this kind of thing.