Newbie Quesion... beware!

[Brutus]

Greetings,

I have done a bit of searching and some manual browsing, but I cannot find the solution to my current problem...

I am creating a related database involving a table of members, a table for event dates, and a table that keeps track of who attended each event. I would like to be able to "control" and automate input into the event list and attendance tables solely by buttons on my member list. I have a button in a list layout that is linked to a script that is supposed to add a record in the related attendance file. I have tried a couple different methods including one involving portals. I don't understand exactly how portals work so it is needless to say that this attempt was fruitless. I still believe portals are the answer, can someone dump some knowledge upon me?

[Wim Decorte]

A portal is basically a view of related records. You have a relationship between members and attendees right? On the members layout you can put a portal to the attendees table and you will see what events that particular member attended. Go to another record in the members table and you will see a different set of events that guy went to.

To add attendances through a portal the relationship needs to allow "create records" in the attendance file (it's in the relationship setup).

You'd probably need to create a value list of events so you can use a drop down menu on the eventID field in the portal.

You can also do it by script by taking the memberID and eventID (store them in globals), go to the attendance file, create a new record and set the memberID and eventID in the attendance record from the globals.

[Brutus]

Alright, I understand the concept and after some doodling around with the file, I have the portal implemented and operational. It now displays the records in the attendance table that correspond to the selected member. Here are the problems:

1) The vertical scroll bar is completely nonoperational! I finally got it visible (that was a common problem so I don't feel too bad about it stumping me) but clicking it does nothing. The size of the portal is irrelevant, whethere its 2 or 20 it only shows that many records and the scrollbar is quite useless.

2) I don't know how to implement in a script the addition of a record in the portal. Could you please post a small example or at least the commands I will want to be using?

Thanks for your help, this community is awesome!

[-Queue-]

1) How many related records is your portal set to display? If it is set to 20 and you have 20 or fewer records, then there is no need for a scrollbar. It will only function when there are more related records than can fit within the portal.

2) Example

From the main (parent file)

Set Field [global1, memberID]

Set Field [global2, eventID]

Perform Script [External: "Child file"] {specify "New Record" script}

Refresh Window

In the "Child" file, "New Record" script

Enter Browse Mode

New Record/Request

Set Field [memberID, Parent::global1]

Set Field [eventID, Parent::global2]

Exit Record/Request

Note that the globals can be passed to the child file, even before there is a valid relationship between them. Globals can be accessed using invalid relationships, which can be very handy for processes such as this.

[Fenton]

> 1) The vertical scroll bar is completely nonoperational!

I don't know what you mean. The scroll bar works. It is likely that you expect it to do something else, like scroll the found set or something; which it won't do, portals show the matched set of records, scrolling doesn't change that.

> 2) The basic concept is that the "attendance" table has 1 record per 1 attendee per 1 event. Yes, it's a lot of records, but it's simple.

What Wim said was to turn on "allow creation of related records" for the Attendance table occurrence, on the Relationship Graph, from Members to Attendance. That way you can add an attendance record simply by choosing the Event, from a drop-down list of events (or their IDs, also showing the name); this would be a field in Attendance.

When the event list gets long that could get tedious. You could however "filter" the event list to make it shorter. You could decide a date range that made sense. For example, only events in the last 30 days.

You'd need a "compound" relationship to events, less than or equal to current date (no need to do attendance for future events; unless people "sign up", which is different business logic), and greater than or equal to the current date minus 31 (or whatever). The source date calculation fields would be unstored, targeting the Event date (in the Events table).

Attached is a file demonstrating how I think I'd do it. It is a little more complicated though. I decided that I also wanted to filter out events for which the member had already been entered. This is called a "dwindling" value list.

It uses a more advanced ValueListItems() function to get the events he HAS attended, then omits them from the relationship (not equals operator). Unfortunately, because of the value list calculation, etc., it does not refresh well. A button with a script is needed to Refresh Window [ "flush cached join results" ] (yeah, sounds awful).

EventsAttendance.fp7.zip

[Brutus]

-Queue-,

I have tried multiple settings for my portal from 2 to 10 rows and there should be 15 or so related records to display. When there are 2 rows for the portal, only the first 2 are displayed. Switching the portal to 10 rows without changing anything else shows 8 more records, and based on the dates displayed I know there are more that qualify. Am I correct in my assumption that the scrollbar is supposed to allow me to view those other records, or does it have some different purpose?

Thanks for the example. It looks like I don't need a portal at all to implement what I desire. The key is triggering a script in the other file, which had not occurred to me. Thanks a million!

Fenton,

I may well be confused as to the purpose of the scroll-bar. I was working off an assumption, could you describe its purpose?

Is there a better way to implement the list of attendances than having a record for each person for each event? Is there a special type of field I could define that could have multiple entries in it? Would that make the database smaller or faster?

I've thought about filtering, but I hadn't thought about filtering the event list. I was more concerned with the member list which might eventually contain several hundred entries, as we keep track of everyone that has attended even a single event, and many never make it to a second. I will doubtless be streamlining the database but my main concern right now is getting it up and functional.

Thanks again everyone for your help.

[Fenton]

First, if you think there should be 10 records in the portal, but there are only 8, you'll want to look at your data. Either something is wrong with the data in the target key field on those 2 records, or you've possibly created a couple of records with the correct data in the key field, but blank in the data field that is displayed in the portal. You can tell if the later is the case, 'cause there will be extra rows that you can click into (normally, with "allow creation of related records" you can only enter the 1st blank row).

>Is there a better way to implement the list of attendances than having a record for each person for each event? Is there a special type of field I could define that could have multiple entries in it? Would that make the database smaller or faster?

Well, you could create a "multi-line" text field in the Event table, and enter each MemberID into it, as return-separated values. But it's harder to deal with, requiring more scripting to add or remove and entry. And you really save very little overhead. With version 7 it is easy to create a proper relational table for this, and store a record.

It's also difficult to create flexible Finds or reports (especially about people) if you've put your attendance into a multi-line field; though you could still get the basics.

In the file I uploaded entry is from the Member record, choosing an Event from an intelligently filtered list (I think so :-). You could also do entry from the Event record, choosing Members. But, unless you build a good "choice" mechanism,* this is not as easy.

Because you cannot really pre-filter Members (unless they've signed up previously, which you never mentioned). So you'd be chosing from the entire member list. A hundred members is not a lot, a few thousand is. A drop-down list for members might work fine in Events, in a portal to Attendance.

*I've uploaded a couple example files, in the Sample Files forum, for choosing people, with a dynamic filter on letters in their name.

[Brutus]

Alright, I have achieved the functionality I desired. The portal was implemented, but I've still not figured out the scrollbar. For right now I don't need to, though.

My current problem is this:

I want to add a field to my member database that will give me the number of meetings they have attended in the current year.

My initial attempt to implement this involved making the field a calculation and trying to sum attendance records in the attendance database. My problem seems to be that I can only set up a relation involving one field, i.e. I can find all records for a given person, and sum those, but I can't figure out how to sum only the attendance records from 2005, for example.

I did a little preliminary fooling around with a portal for this, but I kept getting the same answer for every member depending on which member record I had selected (everyone had been to 4 meetings this year when member 21 was selected).

Thoughts on how this would be best implemented (without messy scripts that I would have to manually trigger everytime I wanted to update the field)?

[Fenton]

It is usually pretty easy to count things like this, using a relationship. On the left side (Members) you need the year. It could be either a calculation of the current year (unstored), or it could be a global field (more flexible). On the right side (Attendance) you need a calculation of the year of the event date, which is just Year (date), both are number.

The relationship would be compound, with AND MemberID. This result will calculate correctly in Browse mode, on the Member's record.

EventsAttendance2.zip

[Brutus]

Sounds like exactly what I was trying to set up. I even had the year fields set up in both files (although I was using a global, which I didn't really like even though I'd only have to manually set it once a year ) but what I couldn't and still cannot figure out is how to set up that compound relationship?

I don't have FM 7.0, is that a new feature in that version?