Hi Alex, this is Guy Stevens from Youtube.
I will be giving you a more detailed explanation this evening (Belgian Time) but I wanted to ask something first:
The people you are relating to this client. Are they also clients? Or are they people from the people table?
If they are you will be needing a join table between People and Clients to be able to assign which people are related to wich clients.
Also, you will not be entering names and emailadresses in this table. But you will be entering ID's. The ID og the contact in one field that's related to the contacts table and the ID of the people in another field that's related to the people table.
That way if a contact or people ever changed their emailadress you only have to change it in one spot.
This is all pretty simple and I can explain it to you. So no worries.
It's also very simple to create a checkbox so you can select multiple people.
Yes the People table is a sepret table from the Client table. The people table has a Association drop down list with Buying Agent, Listing Agent, Appraiser and ect.( a couple other choices)!
Allright Alex, let's learn some stuff.
There are a few things you need to learn to make your database work:
- A Join table
- Some basic scripting
- and a little trick to make a dropdown work.
Here are the tables you need to make this section work:
- Clients: Which you already have, to store the information about your clients.
- People: To store the information on people that aren't clients. (Buying Agent, Listing Agent, Appraiser, etc.)
Here you will store their email address etc.
- And then you need a join table to join the two of them.
Because every client has his own record, every 'people' has their own record.
But then every people can be linked to many clients and every client can be linked to multiple people.
We need to store these relationships in a join table.
This forum member also just had some trouble creating a join table. Maybe you can review this thread and the files and see if it rings a bell:
These are the fields you will want to have in your join table:
ID - It's own ID, a number field set to auto enter a serial number
ClientIdFk - A number field where we will store the ID of the client
PeopleIdFk - A number field where we will store the ID of the related people record (person).
Association - A text field where we can enter the association this person has to this client.
If you have this you can start relating peiople to clients.
Here is my video on the Dropdown without ID trick:
Now we need to create the scripts to link them from a seperate list layout.
Creating a script is just a matter of thinking what needs to happen, which actions you need to take in order to get something done.
In this case we want to:
- Start from a client record
- Then go to a list of people
Here we need to stop for a moment because here the user needs to be able to choose people from the list. This can be done by using check boxes, but then we need to remember that we need to empty them first, because we don't want some previous selection to still be present.
When we selected all the people we want to associate we need to start another script.
Now we need these selected people's ID's to be entered in the join table, next to the ID's of the client.
There are multiple ways to do this, you could do a loop script but I think importing the selected people' ID's into the Join table is the simplest way to go.
At this moment we need two ID's. The ID's of the people and the ID of the client. But we have already left the Client layout a long time ago, how do we get his ID?
Either we set a global variable in the first script that we can then use later in the second script.
Or we set a value in a global field in the People table during the first script that we then pick up again in the second script.
Both are equally fine.
So that gives us the following scripts: (starting from your Client layout with a button)
- Set Variable [ $$ClientId ; Client::ID ] - (when you use two dollar signs the variable becomes a global variable and can be used again after the script ends.)
- Go to layout [Your list of people, based on the people table with a checkboxes in front of every name]
- Show all records (to show all the people)
- Replace Field Contents [ People::Selected ; "" ] - (This clears out all of the previously selected people's checkboxes. This field is a simple text field set to be a checkbox set that contains a value (like "Yes" )
Ok, that's script number one, now you are on the people list and all your checkboxes are cleared.
Now you check your boxes and there should be a button that imports these people in your join table (script 2):
I would use importing because it's pretty simple. First we will perform a find that shows us all the selected people and if we then go to the join table and import from the people table anly the records in your found set will be imported. Then we set the ClientID on the imported records and we are all done.
- Freeze Window (So we don't see the change to the join table layout)
- Enter Find Mode (Uncheck the pause button)
- Set Field [People::Selected ; "Yes" ]
- Set error capture [on]
- Perform Find
If (Get FoundCount) =/= (Does not equal) 0
- Go To Layout [JoinTable]
- Import records (from the people table to the join table - Map only the People::ID to the JoinTable::PeopleIdFk field )
- Replace Field Contents [ JoinTable::ClientIdFk ; $$ClientIdFk ] - This sets the ClientId variable we created earlier in all of the records in the found set.
- End If
- Go To Layout [Clients]
- Go To Object [Association] - You can use this to go back to the Association tab but then you do need to name your tab "Association". You do this by clicking on the tab in layout mode so it gets a black square around it and then you enter a name in the inspector.
Voila, this should be working pretty nicely.
P.s. this is all off the top of my head, so there might be some oversights.
Let me know if you need any more help.
Ok thanks for all the help! I am currently working on the scripting and tring my best to do everything as described! looking alot better and working on the final parts of it! thanks for the help so far!!
Ps - Im having a little issue with the end of the first script and working on the second script!
First script - i can get all the way to my list page and it wants me to replace my data rather then allowing me to select it.
Second script - im stuck at the import records field its not allowing me to choes what two fields I wants to map it will only allow me to do one field only.
In the first script you end with a "Replace Field Contents" step.
There you have to set the correct settings.
The idea of this step is that we make sure that every single checkbox is empty. So that no people are selected from a previous time.
That means that we have to empty the "selected" field in the People table.
Maybe you don't have that field yet, then you need to create it, it should be a text field.
When you add the "Replace Field Contents" script step to your script you need to set the correct settings.
At the bottom you have two specify buttons.
The first one allows you to specify the field you want to enter a value into.
This should be the "Selected" field in the People table.
Then you hit ok to close that dialogue.
Then you'll see a second specify button underneath where it says "Specify target field"
Hit that and you should see:
What we are going to do is "Replace with calculated result".
Because that allows us to choose exactly what we want to enter. You will get a dialogue where you can enter a calculation, but what we need is simply:
without a space in the middle.
This basically means we will enter nothing into the selected field.
And that way all the fields are empty.
Now one more thing to do after you close this dialogue is to make sure this doesn't show up all the time by checking "Perform without dialogue" at the bottom of the script.
That way that window doesn't show all the time.
With the second script I can't really know what's going wrong there, maybe you could show me a print screen.
Normally speaking you should be able to select two tables. One to improt from and one to import into. That should be "People" on the left and the "Join" table on the right.
The mapping is done by clicking the lines and arrows in the middle between the fields.
First you have to specify your data source. That would be your current file. Make sure you add that first. Otherwise Filemaker won't know where to import from.
Also keep in mind that as soon as you do this, you can't go renaming your file anymore. Beecause then the next import won't be able to find the file and won't work.
So: - Specify the data source as the current file
- then Specify the import order
- Then check "Perform without dialogue"
Any problems? Let me know.
I tried to do all I could and will continue to try different things but some things near the end of the script was hard to figure out! I will stay in touch!
P.S. At the moment we switched from going to association as a normal drop down list because that will vary im just stuck finding a way to pull there name and email into the portal?
Wow, you do seem to be missing quite a few script steps.
You enter find mode, then set a field, then set the error capture on, which is all good. But all of that is done in order to perform a find.
But you don't do that.
Instead you go straight to importing records.
That's quite a few steps missing.
Also your Replace Field COntents script ste only has one setting specified. But you need two. You need to specify the field you are going to set and the value you would like to set it with.
I suggest you look at my previous posts again, try to understand what's going on there.
I have all the script steps layed out there.
If you leave a bunch of them out you can be sure it's not going to work.
Pulling in the Name and email adress is no problem if you are using the Person ID in the join table and have created a relationship between the join table and the people table.
Let me know exactly where it's going wrong.