Jump to content

Creating related records through a relationship


This topic is 3537 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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 )
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Meetings.fp7.zip

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 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".

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

"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.

Link to comment
Share on other sites


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.filemaker.com/message/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.

AttendanceREV.fp7.zip

Edited by LaRetta
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

... 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?

Link to comment
Share on other sites

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/forum/topic/42617-basic-monthly-calendar-april-06/?hl=calcslotsvsgenslots.

 

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.

Link to comment
Share on other sites

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.  :-) 

Link to comment
Share on other sites

Message 6:

"More to the point, these attendance tables can be generated from any subset of people and/or any subset of meetings."

 

"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."

 

?

Link to comment
Share on other sites

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

 

Yes, I do think it is important, hence I've fixed it now.

I'm sorry that Filemaker/Database is only a tiny part of my working life and keeping my details up to date on a website I use infrequently is not a priority.

 

Message 6:

"More to the point, these attendance tables can be generated from any subset of people and/or any subset of meetings."

 

"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."

 

?

 

Well, all the records are generated at the beginning, but the attendance tables are just portals that can dynamically show the information based on the current found set.  So when I say the "attendance tables can be generated from any subset" I mean that the user can select which people she wants in the table, and which meetings she wants in the table, and it generates the view of the table based on the data that already exists.

Link to comment
Share on other sites

Hi Joe,

 

So the idea that a User can select which students and which day to view and complete the Roll Call must be flexible.  But, if I understand, that does not mean it has to be created ahead of time (only DISPLAYED like it looks like it has been created).  And that records only need to track absences and doesn't need to create an empty record for all the days when present.

 

When you view the example I presented, there are no records for the empty slots - only records for the red boxes. :-)

 

If you still need records for every single day then the script Comment suggested should do the trick nicely!!  Otherwise just complete the sample display as I showed, use a global to display the span of time you wish to view and perform a find for the Students your User needs to work on.  


I agree with Comment on your response, btw.

Link to comment
Share on other sites

Forum Joe,

 

  That wasn't a "snarky comment" and Lee is the last person to make himself feel superior via a comment to a post. If Lee chose to use sarcasm in this case I believe it's completely acceptable. This is a technical forum. 

Link to comment
Share on other sites

This topic is 3537 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.