Jump to content
Sign in to follow this  
Lizzy

Table Occurance Groups...

Recommended Posts

Hiya,

I have two tables, one called "Events" and once called "Attendees", that link through a join table called "Registrations". So far, they are set as many to many in the relationship graph, as many attendees attend an event, and attendees attend many events. That's all working great.

I receive a list of Attendees from an Organizer (value list), and import those contacts into the database. I would like to create a layout, and a script to automatically assign the Event they attended to the group of imported Attendees.

How do I go about building the Table Occurrence Group on the RG. My primary keys are Attendee ID No, and Event ID No. So far I've not used global fields, but think this is the only way to assign the Event ID to the Attendees.

Thanks!

Share this post


Link to post
Share on other sites

I think this points in direction of a join-free many2many structure, where a pilcrow delimited list is modified by either a looping or a scripted replace???

--sd

Share this post


Link to post
Share on other sites

Ok... Isn't there an easier way... lol!

My thinking was along the lines of building an import script. I always import from the same file as some of the excel doc's i recieve are ... well rather creative!

In the script, window pops up, asks what event ID number the records relate to, and then the event ID is assigned.

We have hundreds of events and I'm importing records from 1996...

Share this post


Link to post
Share on other sites

Isn't there an easier way...

There might be, but your description is not clear enough. Are the attendees you import already entered in the Attendees table?

If not, I think you need to import twice: once from the external list into the Attendees table - this will assign a AttendeeID to each imported person, and leave you with a found set of the imported records.

The next step would be to import from the Attendees table into Registrations. You need to add a global gEventID field to the Attendees table, fill it with the relevant event's ID, and import the attendee records (using AttendeeID and gEventID fields as the source fields).

If some of attendees already have records in the Attendees table, then the problem can be more difficult.

Share this post


Link to post
Share on other sites

If your Registrations table is indeed a "join" table, as it appears to be, then you could do more or less as you said, run a Replace to put the Event ID into each of the imported records (into the join table). But there are a couple of things to watch out for, as you've probably noticed, and a slightly different way to do it.

The first is that I think you should begin this process from an Event record, with a button there, and only there. That way you will know for sure what event you're dealing with. Set its ID into a global field; this could be done in any table, but I say Attendees, to use in Import later.

But a bigger problem is how to match to existing Attendees. I would do the Import into the Attendees table.

Button on Events layout (only);)

Set Field [ Attendees::_gEventID; EventID ]

Go to Layout [ Attendees ]

Show All Records

Import [ (•) Matching records, Add new records; match field = Attendee full name ]

**If you run into problems with near duplicate names, you may have to stop the script here to deal with it; kind of a problem with names, automation killers

Go To Layout [ Registrations ]

Import [ from Attendees, ID field AND _gEventID ]** (or use a Loop)

Commit Records

Go to Layout [ Original ]

*It is quite possible that "near" duplicates, "Richard Smith", "Dick Smith" (same address), will require you to stop, to do all kinds of mean nasty duplicate cleanup; which requires that you create a useable foolproof way to mark these records AND be able to add the originals of the near dupes (because the later one you just imported will need to be deleted).

Replacing into the imported found set immediately from a global field first set to the current Timestamp, which could then be set in earlier dupe originals, to add them to a findable set, would work. Then you could "continue" the script with a button. Showing the global EventID would not hurt, just for visual reference. If done on a dedicated layout, it would not be as bad as nailing your feet to the floor :-]

I say "continue", but in actuality the script would have been split in half. Buttons on Event layout, and on Attendees Duplicates layout.

**You've imported both IDs at once, the global _gEventID will be the same for each Reg. record imported here.

Share this post


Link to post
Share on other sites

cool! thanks for the replies.

Sorry for the vaugity (lizzy-ism), around the original post,

Attendees are sometimes in the attendees table as, an 'Organiser' for example, always has been to the Event initially as an attendee. There are always at least 7 duplicate records on every import, as those ppl are the 'team' behind the event. To a maximum of 23 at each event. So, in saying that, duplicates could become an issue.

My original post was around the table occurance group. Do I need to create a group designed only for the new layout (i.e.New Event/Registration...)

The relationships between them has me a bit puzzled too, as i've not at all worked with global fields, despite extensive reading on the concept of applying one record to all records, it makes my heart skip a beat. I'm ready for the challenge i think ;)

Thanks heaps, i guess I was on the right track and probably could have got somewhere similar prior to nailing feet to the floor!

Share this post


Link to post
Share on other sites

How do i build a many2many structure join free...? I've not heard that before, what would the benefits be, and what circumstances could it be applied in?

Do they contain global fields too lol!

Share this post


Link to post
Share on other sites

Attendees are sometimes in the attendees table as, an 'Organiser' for example, always has been to the Event initially as an attendee. There are always at least 7 duplicate records on every import, as those ppl are the 'team' behind the event

If an Attendee Organizer* can be an Attendee of one Event, but the Organizer of another, you really need another join table, EventTeam, which would have the pre-assigned team members, including the organizer, who would have an [x] Organizer checkbox (number) field.

The Registrations table would auto-enter (perhaps with [ ] Do not replace existing data OFF) this data at import time, via a relationship built on EventID AND AttendeeID.**

Then it's no problem. I do not see that the "team" members being imported into Attendees would create duplicate Attendees, not if their names were spelled right. The (•) Import matching records is designed to prevent that.

As far as the import into Registrations, you would expect duplicates of an AttendeeID. These are 2 separate imports, into different tables.

*It sounds like you should keep attendees and organizers in the same table, and use either a checkbox to mark an organizer, or a join table, as I said.

**The reason to bring an organizer 'flag' into Registrations is so you can separate them during reports, Subsummaries, counting.

(P.S. I don't know whether you'd want another table occurrence group for this. Most of the relationships you'd want in the main TOGs anyway.)

Edited by Guest
TOGs

Share this post


Link to post
Share on other sites

The mechanics of assigning the EventID are coming together... I've been tossing with the idea of adding a table especially for the different aspects / member levels to the company, or whether to use a check box to list them, and assign that as a category to the "Attendees" It's not going to work, as one "Attendee" can fall into too many categories. The company basically, have two functions 1) Host events, 2) Train ppl to hold events. The general flow goes like this:

"Attendee" goes to an event > they usually do one of two things a) go again :) don't... If they go again they may go on the "EventTeam". If they go on the "EventTeam", it's as an 'assistant'. The "EventTeam" consists of 2-3 'Facilitators' 2 'Organizers' and 3 'Assistants'... People who decide to become involved at an 'Organisers' level, sometimes join the company to complete the "Training" to become a 'facilitator'.

So, if I create a table that holds the Company training/facilitation process somehow (haven't thought that through 100% yet." Add another to hold info on the 'EventTeam", in effect either one of those could be a 'join' table to serve in this, and many other solutions.

From a users prospective, the layout to assign an New Event, would contain fields from the "Attendees" table, "Events" table, "EventsTeam" table, which would have to have a relationship to the "Company" table. So one event could potentially be added in it's entirety at one point. Although in reality, events are added first, as the schedule comes out.

I need to go back to the drawing board... With the addition of an EventsTeam table, it changes things and I'm sure there are other places this table will function.

Thanks heaps, it all helps in the overall functioning...

Share this post


Link to post
Share on other sites

I've been tossing with the idea of adding a table especially for the different aspects / member levels to the company, or whether to use a check box to list them, and assign that as a category to the "Attendees" It's not going to work, as one "Attendee" can fall into too many categories.

That is why a join table for people pre-assigned* to the upcoming event for some specific function is useful, EventTeam. It sounds like you have 3 levels. Their level would be entered into a "Role" field. A relationship from Registrations could lookup (or auto-enter by calculation) their level into Attendees. Registrations would have their AttendeeID at this point.

If people in Companies always perform the same role, then that could be specified in an Empoyees table. But it sounded like they changed roles depending on the Event, and according to how long they'd been involved in the whole process.

*You may want a mechanism in Regstrations to add a person to EventTeam, in the case that they hadn't been pre-assigned properly.

Share this post


Link to post
Share on other sites

Fenton Fenton! It worked, it worked! Lol!

Took a little tweaking, but the skeleton was all there, and thanks to you it's now a one-button process.

Yah!

Duplicates causing a few issues, but I think that is more on the initial data entry rather than the scripting process.

Am onto the EventTeam bit now :B Thanks again...

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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