Jump to content

  •  

Photo

Creating related records through a relationship


  • Please log in to reply
24 replies to this topic

#1 Forum Joe  novice

Forum Joe
  • Members
  • 21 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 2h 2m 34s

Posted 20 July 2014 - 05:42 PM

Hi, I hope someone can tell me a simpler solution to the following problem.

 

I have a table that keeps track of people, each one has a unique ID number.

I have a separate table that keeps track of dates of meetings.

Those two tables already exist and contain data.

 

I also have table that tracks each person's attendance at meetings and other notes that can be made.  This has zero records in it so far, but I need a script that will create a record for each person at each meeting.  It will have a field that links the record to a person (via ID number) and a date.  

 

I would like a script that efficiently and effectively creates all the records in this third table based on the existing data in the first two.  I can't see how I can use the "Create related records through relationship" to do this, but it might be possible.  Without that, the script is pretty messy and slow since creating new windows to create the relationship takes up a lot of time.  My current solution is something like this.

 

Go to PEOPLE layout

Go to first record (first person)

Loop

.....Copy ID Number of person to variable

.....Create new window, go to MEETING layout

.....Go to first record (first date)

.....Loop

..........Copy Meeting Date to variable

..........Create new window, go to ATTENDANCE layout

..........Create new record (Attendance)

..........Paste Meeting Date,

..........Paste Person ID

..........Close window (attendance)

..........Go to next record(exit after last) - next date

.....End Loop

.....Close Window (dates)

.....Go to next record (exit after last) - go to next person

End Loop

 

Now that looks simple, I know, but if I've got 50 people I'm keeping track of for just 20 meetings, there's still 1050 New Window calls, which take up a significant amount of time.  There must be a better solution.


  • 0

#2 ggt667  newbie

ggt667
  • Members
  • 61 posts
  • LocationTellus
  • FM Application:11 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 19h 14m 15s

Posted 20 July 2014 - 06:22 PM

Use export from the 2 tables possibly as 1 export with related data? and import to the 1 table in question. ( Keep in mind you will only need a couple of fields )
 
Best practice for a case like this would be to export to Get( TemporaryPath ) opposed to Get( DocumentsPath )

  • 0

Co-author: FX.php, pyFileMaker, netatalk, +++

 

http://wethecomputer...t.blogspot.com/


#3 BruceR  consultant

BruceR
  • Members
  • 3,382 posts
  • LocationRedmond WA
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Expert
  • Certification:9, 11, 12, 13
  • Membership:TechNet
  • Time Online: 34d 21h 19m 29s

Posted 20 July 2014 - 07:17 PM

It would  be helpful - and a good exercise for you - if you would upload your file or a simplified example of your file.

 

1. How do you know who is at the meeting? Do you already have this list in some electronic format? Did you somehow use this to establish your found set in People?

2. Why record the date in the Attendance record? If the meeting only has one date, then it would seem there is no need to attach a date field to the attendance record. The attendance table might consist of MeetingID; ContactID; and ContactNotes

3. There are a lot of problems with using copy and paste; copy/paste should be avoided. But what to use instead? Time to learn about using script variables.

Demo file shortly.


  • 0

#4 BruceR  consultant

BruceR
  • Members
  • 3,382 posts
  • LocationRedmond WA
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Expert
  • Certification:9, 11, 12, 13
  • Membership:TechNet
  • Time Online: 34d 21h 19m 29s

Posted 20 July 2014 - 07:35 PM

Attached is a very simple example of a file using a script with script variables.

 

It assumes that you are on the correct record in Meetings; and that you have the correct found set in People.

It then does (functionally) what your original script does, but without copy paste and without requiring new windows.

It does not provide much in the way of comments or explanation.

 

Note that this script is really too simple. It has a few problems, in its present form. For instance, every time you run it, it will ALWAYS create a complete set of new attendee records, and you will therefore end up with duplicates. 

Enhanced examples to follow.

 

It is likely that other contributors will provide examples.

 

Attached Files


  • 0

#5 comment  consultant

comment
  • Members
  • 24,562 posts
  • Time Online: 344d 14h 17m 14s

Posted 20 July 2014 - 07:37 PM

I also have table that tracks each person's attendance at meetings and other notes that can be made.  This has zero records in it so far, but I need a script that will create a record for each person at each meeting.

 

Before getting to the question of how, I would like to ask the question why? What is the purpose of pre-creating a grid of 20x50 records that carry no information whatsoever? IMHO, you should create a record only when you have something meaningful to store in it. e.g. this person has attended this meeting.

 

In any case, even if you decide you do need such a thing, there is no need to open a new window each time - or at all. You should also avoid using copy and paste in scripts in favor of setting variables and then using those to set fields. Note also that if you are using all records in a table, you can loop over a value list of that table's IDs - which will save a lot of shuffling back and forth. Otherwise, if looping over two found sets, your script could look something like this (untested):

# FIND THE RELEVANT MEETINGS
Go to Layout [MEETINGS]
Perform Find [...]
#
# FIND THE RELEVANT PEOPLE
Go to Layout [PEOPLE]
Perform Find [...]
#
#
# BEGIN OUTER LOOP (PEOPLE)
Go to Record (First]
Loop
    Set Variable [$personID; People::PersonID]
    #
    # BEGIN INNER LOOP (MEETINGS)
    Go to Layout [MEETINGS]
    Go to Record (First]
    Loop
        Set Variable [$meetingID; Meetings::MeetingID]
        Go to Layout [ATTENDANCE]
        New Record
        Set Field [Attendance::PersonID; $personID]
        Set Field [Attendance::MeetingID; $meetingID]
        Go to Layout [MEETINGS]
        Go to Record (Next; Exit after last]
    End Loop
    # END OF INNER LOOP
    #
    Go to Layout [PEOPLE]
    Go to Record (Next; Exit after last]
End Loop
# END OF OUTER LOOP
I can't see how I can use the "Create related records through relationship" to do this,

 

There is no advantage here in creating records through a relationship - on the contrary.


  • 0

#6 Forum Joe  novice

Forum Joe
  • Members
  • 21 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 2h 2m 34s

Posted 20 July 2014 - 11:26 PM

Thanks everyone for your assistance.  To try to help explain why I'm doing it with this method, let me show you the requirement.  I need to be able to generate attendance tables like this:

NJ4GNUi.gif

 

Where the peeople are down the left and the meetings are along the top.  More to the point, these attendance tables can be generated from any subset of people and/or any subset of meetings.  I can do this with a complex arrangement of vertical and horizontal portals, but it means I need to have a record of attendance (a tick or a cross) for each person at each meeting.

 

If you've got a better way of doing this, I'd love to hear it, but I'm only really a basic FMP user so I might not be able to implement some of the stuff suggested.

 

As for the comments about not copying/pasting, I agree.  I wrote in the script "copy to variable" but I really meant "set variable to".  I don't use copy/paste within scripts.

 

Why record the date in the attendance record?  Just because, in my example, that's the unique identifier of the meeting and it assumes there's only one meeting a day.  I fully agree that giving each meeting a UID is better, but I was trying to keep things simple for my example.

 

BruceR, The way the database is set up, the complete records of people and the records of meetings will not ever be added to or subtracted from.  This script only needs to run once when setting up the database initially, so I really would like it to do all people and all meetings, not a subset of people for one meeting.

 

Thanks for the advice though.  I really do see that there are many ways to do this crappy thing that I want to do, and I do like comment's solution.  I hadn't really considered that going back to a layout keeps the found set and the position within that found set.  That makes just changing layouts much easier than creating new windows.


  • 0

#7 comment  consultant

comment
  • Members
  • 24,562 posts
  • Time Online: 344d 14h 17m 14s

Posted 21 July 2014 - 01:14 AM

 I need to be able to generate attendance tables like this:

 

That picture was made using a spreadsheet, wasn't it? How exactly do you expect to produce this using Filemaker?

 

 

I can do this with a complex arrangement of vertical and horizontal portals

 

If you mean multiple portals placed side-by-side, then you can do this only if the exact number of columns and rows is known in advance. Which would contradict this:

 

these attendance tables can be generated from any subset of people and/or any subset of meetings.


  • 0

#8 Forum Joe  novice

Forum Joe
  • Members
  • 21 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 2h 2m 34s

Posted 21 July 2014 - 01:49 AM

That picture was made using a spreadsheet, wasn't it? How exactly do you expect to produce this using Filemaker?

 

 

 

If you mean multiple portals placed side-by-side, then you can do this only if the exact number of columns and rows is known in advance. Which would contradict this:

 

No, that picture is a screenshot from Filemaker!  It's currently using a portal with a repeating field, but I'm hoping to restructure the db to use a portal within a portal instead to achieve the same (but improved) effect.

 

I don't need to know the exact number of columns and rows, but I do have to manually create a layout with the maximum number of rows and columns.  Extra rows and columns just don't get shown if the data doesn't exist, so they show up as blank, which is fine.  Just like if you have a portal showing 10 rows but there's only 5 related records.  It's not a problem.


  • 0

#9 comment  consultant

comment
  • Members
  • 24,562 posts
  • Time Online: 344d 14h 17m 14s

Posted 21 July 2014 - 02:02 AM

 It's currently using a portal with a repeating field,

 

Okay, but that's not interactive; IOW, you can display the attendance, but not toggle individual cells (unless you really invest into this). And you do not "need to have a record of attendance (a tick or a cross) for each person at each meeting" for this. Having records of actual attendances (or absences) only would be quite sufficient.

 

 

I'm hoping to restructure the db to use a portal within a portal

 

There's no such thing as "a portal within a portal".


  • 0

#10 Forum Joe  novice

Forum Joe
  • Members
  • 21 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 2h 2m 34s

Posted 21 July 2014 - 03:16 AM

Okay, but that's not interactive; IOW, you can display the attendance, but not toggle individual cells (unless you really invest into this). And you do not "need to have a record of attendance (a tick or a cross) for each person at each meeting" for this. Having records of actual attendances (or absences) only would be quite sufficient.

 

 

 

There's no such thing as "a portal within a portal".

Yeah, I've just realised that.  So it isn't going to work, I'll go back to my repeating field method.  That's ok.

But the previous setup (in the screenshot) does work and is fully interactive to toggle between ticks and crosses.   Saves a lot of hassle but restricts flexibility a little.


  • 0

#11 comment  consultant

comment
  • Members
  • 24,562 posts
  • Time Online: 344d 14h 17m 14s

Posted 21 July 2014 - 05:21 AM

But the previous setup (in the screenshot) does work and is fully interactive to toggle between ticks and crosses.

 

If it is, then you do not have the structure described in in your original post. And there is not much you can do with this data except produce a pretty display. So at this point you have lost me regarding the purpose of this entire exercise.


  • 0

#12 BruceR  consultant

BruceR
  • Members
  • 3,382 posts
  • LocationRedmond WA
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Expert
  • Certification:9, 11, 12, 13
  • Membership:TechNet
  • Time Online: 34d 21h 19m 29s

Posted 21 July 2014 - 05:55 AM

"BruceR, The way the database is set up, the complete records of people and the records of meetings will not ever be added to or subtracted from.  This script only needs to run once when setting up the database initially, so I really would like it to do all people and all meetings, not a subset of people for one meeting."

 

Then add a find all records step to the people part of the script.


  • 0

#13 BruceR  consultant

BruceR
  • Members
  • 3,382 posts
  • LocationRedmond WA
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Expert
  • Certification:9, 11, 12, 13
  • Membership:TechNet
  • Time Online: 34d 21h 19m 29s

Posted 21 July 2014 - 06:06 AM

Here's an approach that does use the "create related records" feature.

Attached Files


  • 0

#14 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,989 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 243d 12h 58m 37s

Posted 21 July 2014 - 11:41 AM


Okay, but that's not interactive; IOW, you can display the attendance, but not toggle individual cells (unless you really invest into this). 

 

Yes, but sometimes the investment is worth it.  In this demo, it is quite easy to replicate and put into place.  Also (smile), filtered portals have been vastly improved (please see below)

 

Hey Joe, we can use filtered portals and it can probably be optimised further.  This was my quick first stab ... and you can make ticks and Xs but this is quick so please excuse the kludge.  There is a grouped portal and field - I made it grey so it is easy to see.  This is how to replicate:

  1. Copy the grey portal and field inside and paste onto the next day.
  2. Select it, right-click and select Portal setup.  Change the filter calc to the next repetition number.
  3. Select it, right-click and select script trigger and set the script parameter to the next repetition number
  4. Select it, right-click and select conditional formatting and change to the next repetition number.

You can also change that repetition calculation to exclude weekends but I'm no Repetition Master so it would take me more time than I have to adjust the calc.  Comment would surely know since these types of techniques were originally taught to me by him anyway.

 

---- further information on portal filtering ----

 

BTW, filtered portals had been improved in 12 to where it would perform the task on server at times (here is cut and paste - it was from TechNet for those that are registered https://fmdev.filema...e/123083#123083:

 

 

jbante-

What I heard from I-don't-remember-which-FMI-employee during I-don't-remember-which-webinar is that the server only takes over the calculation in certain very simple filter calculations that 1) only reference fields in the table occurrence being filtered (i.e. the TO of the portal), and 2) only compare those fields to constant values. (So comparing BaseTable::field > PortalTable::field would not get the optimized treatment.) There was some uncertainty as to whether the server-side optimization would work if the comparison referenced $$variables (and some embarassing ignorance from the FMI rep who wasn't sure if it was valid to use $$variables in a portal filter calculation!). I could be misremembering some significant details. 

 

tsgal-

just spoke with Matt O'Dell, and he confirmed what "jbante" posted.  That is, the only change in Portal Filter Optimization between FileMaker Server 11 and FileMaker Server 12 is when comparing a field in the referenced portal to a constant value, as this is executed at the Server before sending the results to the client.  All other occurrences, including referencing global variables, are performed on the client side.

 

 

But 13 has further optimised portal filtering.  This was copy/pasted into my FM bible so it came from either Comment, Ray Cologon, or an FMI engineer.  I haven't had time to find it again to provide the exact reference.

 

Portals now are treated like relational as long as the value you are referencing does not have to be downloaded to client to determine such as sort.

 

​I am not sure whether the global date will make a difference in this case.  It is possible that, since this is for data-entry, you can use an unstored calculation instead of gDate as Get ( CurrentDate ) and remove the global from the filtering completely.  Prior to 12, portal filtering ... oh dear, I just realised you ARE on version 11.  Well, if you can keep the related record set smaller (under 1,000 or so), it should be fine too.  It would not be difficult to constrain this relationship to only current attendance.

 

And now this has taken way more time than I had, my apology.  But I hope it is helpful to provide other options.  I've used this for attendance selection in the past and I know it works.  We might even be able to use GetLayoutObjectAttribute() so only one naming takes place instead of three.

 

Small bug - demo removed - I'll repost it within 2 hours.  I forgot we need to use triggers instead of buttons.

 

Demo replaced.  I changed the buttons to script triggers.  This is because the script evaluation must take place from the context of the filtered portal instead of the parent record.

Attached Files


Edited by LaRetta, 21 July 2014 - 12:46 PM.

  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#15 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,989 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 243d 12h 58m 37s

Posted 21 July 2014 - 12:34 PM

The other thing I wanted to mention is that, as you move the attendance forward, just change the global date forward (if you use a global over an unstored calculation).  You can attach buttons to jump forward/back a week at a time as well.  BTW, the second quote about 13 came from someone at FMI.  If it came from Comment or Ray I would have listed their names in my FM bible.


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#16 Forum Joe  novice

Forum Joe
  • Members
  • 21 posts
  • FM Application:12 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 2h 2m 34s

Posted 21 July 2014 - 05:03 PM

Thanks LaRetta!  That's awesome.  You made me realise I hadn't updated my profile, I'm on 12 Advanced, so some of that stuff will be useful.  I've opened up your example and will have a good thorough look when I can make some time.  It's very useful stuff!


  • 0

#17 Lee Smith  I like the v13 layout tools

Lee Smith
  • Staff
  • 10,293 posts
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch
  • Platform:Mac OS X Mavericks
  • Skill Level:Expert
  • Membership:TechNet
  • Time Online: 244d 6h 16m 43s

Posted 21 July 2014 - 07:50 PM

... You made me realise I hadn't updated my profile, I'm on 12 Advanced ...

Wow, don’t you think that that was kind of important information for us to know?


  • 0

#18 BruceR  consultant

BruceR
  • Members
  • 3,382 posts
  • LocationRedmond WA
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Expert
  • Certification:9, 11, 12, 13
  • Membership:TechNet
  • Time Online: 34d 21h 19m 29s

Posted 21 July 2014 - 08:23 PM

Indeed.


  • 0

#19 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,989 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 243d 12h 58m 37s

Posted 21 July 2014 - 08:24 PM

Luckily it works the same in 11, 12 or 13 but I believe it is simply further optimised on the server side as you move up versions, which is true for many things in FM. :-)

 

The point is that records are only created when needed (as Comment explains) so this process creates records for Absences (to produce the smallest record set).  Now when taking roll, usually teacher scrolls down the student names and just checks those that are not there as they call out their name.  When finished, the task of 'roll call' is marked complete so there is no need for 'green checked' records.  But maybe they want to be able to 'check' each person on that day and if so, it is easy to include that as well, setting a 1 or 0 in a number field in Attendance.  I have no qualms in creating related records this way since it is a single-user walk-through process.  And still, if no record is needed (roll wasn't taken on someone or a class or a day), no meaningless, empty records are created.

 

There are many ways to modify this concept which was picked up from Comment's CalcSlotsVsGenSlots in 2006 http://fmforums.com/...slotsvsgenslots.

 

And yes, I know the dates in the squares looks dorky - it's so you can see the functionality and because I forgot how we can make dates appear as boolean.  And I would still only display a week or two at a time so it remains viable on iOS.


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#20 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,989 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 243d 12h 58m 37s

Posted 21 July 2014 - 08:34 PM

Personally, I believe there is less point of failure creating records through relationships than using script.  It is like the data holds hands and can't be written more than once.  It feels far safer to me.  In the olden days, it would require great arrays of table occurrences and calculations and was something I avoided because of the heavy weight.  But with FMS improvements, filtered portals are back in my good favour, although I still treat them with a bit of caution particularly since I'm not completely clear on what will force download to client for evaluation.  My list of tests never seems to decrease.  Just when I get around to running tests, another version is out.  :-) 


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.




FMForum Advertisers