Example: If I assign 20 oranges to a group of 5 people, I wish there is an automatic creation of records of 4 oranges for each person
First you need to choose the people that will share the assignment either with a checkbox set a portal and then you need a script with a loop to create the records
That can be scripted.
Let's say you have a found set of five people, and you enter in a global field for the number of oranges you want to assign. A script to create the related records may look like this:
Set Variable [ $oranges ; People::GlobalOranges ]Set Variable [ $perperson ; Round( Get(FoundCount) / $oranges ; 0 ) ]Go To Record/Request/Page [ First ]Loop Set Variable [ $id ; People::PrimaryKey ] Go To Layout [ Oranges ] Loop New Record/Request/Page Set Field [ Oranges::ForeignKey ; $id ] Set Variable [ $personcreated ; $personcreated + 1 ] Set Variable [ $totalcreated ; $totalcreated + 1 ] Exit Loop If [ $personcreated >= $perperson or $totalcreated = $oranges ] End Loop Go To Layout [ original layout ] Set Variable [ $personcreated ; "" ] Exit Loop If [ Get(RecordNumber) = Get(FoundCount) ] Go To Record/Request/Page [ Next ]End LoopShow Custom Dialog [ "Creation Complete" ; "Created and assigned " & $oranges & " oranges in groups of " & $perperson & "." ]
Set Variable [ $oranges ; People::GlobalOranges ]
Set Variable [ $perperson ; Round( Get(FoundCount) / $oranges ; 0 ) ]
Go To Record/Request/Page [ First ]
Set Variable [ $id ; People::PrimaryKey ]
Go To Layout [ Oranges ]
Set Field [ Oranges::ForeignKey ; $id ]
Set Variable [ $personcreated ; $personcreated + 1 ]
Set Variable [ $totalcreated ; $totalcreated + 1 ]
Exit Loop If [ $personcreated >= $perperson or $totalcreated = $oranges ]
Go To Layout [ original layout ]
Set Variable [ $personcreated ; "" ]
Exit Loop If [ Get(RecordNumber) = Get(FoundCount) ]
Go To Record/Request/Page [ Next ]
Show Custom Dialog [ "Creation Complete" ; "Created and assigned " & $oranges & " oranges in groups of " & $perperson & "." ]
Scripting in filemaker is all procedural, so in the above script, I would be:
1) Figuring out how many oranges I need to create ($oranges)
2) Figuring out how many per person I need to assign ($perperson)
3) Going to the first record in my found set
4) Looping over each record in my found set of people
a) Getting the ID from People to create a relationship ($id)
b) Going to the oranges layout
c) Creating the related oranges records I need and assigning them to be related to that person
5) Returning to the People layout and repeating for each person in the found set
So in essence, I am automating that entire process. This logic can be adapted to do a practically unlimited number of actions. The use of script triggers and other "events" can also be used to do much more complex actions.
Thank you Mike for your answer. I have further informations for you :
1. I want to choose the persons i want to give the orange to. I want to build many groups of person among a global list of persons. If i define a global field for the number of persons, i will define it for all the persons.
I'm not fluent in English but i hope you understand.
And how will you choose people for a group? (There are many ways)
Will people stay a part of the group until you assign them differently or do you only need them in a group for long enough to create these records?
Can the same person be a member of more than one group at a time?
Thank you for the response Phil.
I want to do it using the following steps :
1. Create a group and assign the person that i want to be part of the group.I want to select the ID of every person using a list with multiple choices. the number of oranges is assigned to the group.
2. Create records with the number of orange for each person
A person can be a member of many different groups.
Hope you understand better now.
I'm not 100% sure that I do, but if:
People are assigned to groups and stay assigned. (You aren't just creating temporary groups just long enough to do this task.)
Then the first part of this is setting up a data model that supports assigning multiple people to multiple groups.
This can be done with either a multi-value key or a join table. Both work, I tend to prefer a join table, but you might find that a multi-value key is sufficient for this task.
Members::MemberIDpk = Member_Group::MemberIDfk
Groups::GroupIDpk = Member_Group::GroupIDfk
Each time you add a member to a group, you create a record in Member_Group linking the group to the member. This is often done with a portal to Member_Group. However, if the number of groups is manageably small, you can set up a slightly different set of relationships (with the same three tables) that puts up a list of groups in a portal with a button that looks and acts like a check box. Clicking it the first time creates a record in Member_group and the button changes to show that the group was selected for that member. Clicking it again both deletes this member_group record and "clears" the apparent check box. Let me know if that interests you and I'll share a file with a working example of this selection method.
Members::GroupIDList = Groups::GroupIDpk
GroupIDList would be formatted with a set of check boxes where it's defined value list is set up to list the groups and enter the Group ID when a check box is selected. (This builds a return separated list of IDs in GroupIDList.) This is easier/quicker to set up, but you lose some options for reporting and you can have problems if the number of group records grows as your field listing the groups as check boxes can neither grow nor use a scroll bar to display such a growing list of groups.
With either of these methods, you can pretty easily get either a found set of members for a selected group or get a list of member IDs in a (probably global) field that you can use in order to divide up your "oranges" amongst the members of that group. That then makes the script already suggested by Mike workable for you.
Let me know if that interests you and I'll share a file with a working example of this selection method ----> Yes, it interestts me.
Please, receive the attachment ( i tried to do what you explained but i don't understand the muli-value key part). Please, can you help me with this ?
You appear to have the correct relationships and tables for a join table. I'm not going to just finish all the work now needed to implement what you want as I don't have the time, but I'll be glad to assist you as you do this for yourself. You'd next need to use a portal to the join table on either the member or groups layout to use for assigning members to groups.
Exactly what do you need help with next?
(And the multi-value key approach would not use the data model that you have in this sample file.)
As to the example file, see "Check boxes with Scroll Bars II" in the following file:
Adventures in FileMaking #2-enhanced value selection
When i try to build the relation : Members::GroupIDList = Groups::GroupIDpk
A see a pop-up of a copy of "Group" table. I don't know if there is something wrong.
I already downloaded the file. I will take a look to it and get back to you.
You don't need GroupIDList if you are going to use a join table. You only need use one method or the other. I was listing two options, each with different Pros and Cons for you to choose from, there is no need to use both in the same solution.
What you got was the typical result when adding a relationship would create more than one "path" from the one table occurrence box to the other. FileMaker cannot allow that and still function correctly. So it solves the issue by offering to create a new occurrence of one of the table occurrences. This new table occurrence is simply a new reference to the same table so it does not mean that you've done anything wrong and you can use that new occurrence to make things work. (but sometimes it's the other table occurrence of the two that you need to duplicate...)
Tanks to Phil and Mike.
I've been able to create the records but i'm facing a new issue.
In the script, how can i edit automatically the number of oranges of each persons , the first names, the last names, the date of birth, ...?
If you refer back to my original example script, the concept of setting variables and setting fields should be all you need.
As for name and date of birth, you shouldn't need to set those things via script since you are establishing a relationship to that data. One of the main reasons to use related data is so you DON'T copy the same data across multiple tables. This is called de-normalization, and can lead to major issues in your database later.
I'd highly recommend that you take some training courses on RDBMS architecture and FileMaker (scripting, calculation engine, relationships, lookups and how to reference fields across relationships). Cris Ippolite's courses on Lynda.com are really well done. filemaker 16 cris ippolite - Lynda
Retrieving data ...