Complex Relationships - Multiple Criteria - Calendar, Users, Billing Info

[mz123]

I have three tables of data.

1. Calendar (SeedCode)

2. Billing data

3. Users Table

I need to have the calendar show billing data summarized by user.

For a given day, the calendar should look like the following:

[Name] [Hours]

User1 2.0

User2 3.2

User3 4.0

Note: Each user can have multiple billings in one day so if I didn't show summaries it might look like this:

[Name] [Hours]

User1 1.0

User1 1.0

User2 0.2

User2 2.0

User2 1.0

User3 4.0

I can't figure out the relationship to set it up. I can get all the individual billings for a given date (as shown in the second example) but I can't get the summaries. Do I need to create a calculated field for each user? Obviously I'm trying to avoid that, but I'm not sure if there's any other way around it.

Any help would be GREATLY appreciated. Thanks!!

[John Sindelar]

Hi Martha.

This kind of summarizing is easily done in a report layout in your billings table, but it sounds like you want to see these summaries in the calendar in browse mode.

Here are two approaches to this, depending on if you need to see these summaries just one day at a time, or if you need to see them on multiple days, as your would on a week or month view.

1. Multiple days.

You've obviously got things working if you can show the actual billings as in your second example. The trouble in summarizing them is that the calendar will need to be looking at user records, not billings records. And moreover, the calendar need to look at not just user records: the calendar will need to be looking at a user-day record in order for that user-day record to itself look into billings and sum the users hours for that day.

The calendar thinks of this user-day record as an appointment. Once you have appointment records for each user on each day, you can create a relationship from appointments to billings based on the user and the day. Then make a calc in the appointments table that sums the hours across this relationship: this is the calc that you'd show in the calendar beside the user on each appointment for the day.

Since the hours calc is a new field IN the calendar's appointments table, you can show this calc on any calendar layout: day, week, month, etc.

The trick here is making the appointment records for each user. A couple ideas:

- Use the repeating events feature in SeedCode calendar to jus create a year's worth of appointments for each user (repeat every day, or every week day, for one year), doing this once per user.

- Taking the above a little further, you could record something useful while you're at it and create an appointment type called "schedule" and use the repeating events feature in SeedCode Calendar to create each user's work schedule. This way you'll have appointment records for each day the user works, and you can filter these off of the calendar using the "type" filter if you need to.

- You could even import these user records from your billings table periodically (at the end of every day or week). First you'd perform a summarized export from billings (sorting and then summarizing by user and day) and then import these user-day records into the calendar as appointments.

2. One day at a time.

This way of setting things up only works on the daily view, so as you change days you'll see the new hourly totals for each user that day, but you wouldn't be able to do this on a week or month view.

To begin you'll create a portal on the daily view of the calendar to your users table. This will always show all users, regardless of the day you're looking at. Then create a global date field in your users table. You'll edit a script in SeedCode calendar so that when you flip to a new date in the calendar you'll send that new date to this global field in your user's table as well.

Edit the calendar script "Move Date Left or Right ( Target ) { Direction }" and set your new global field to equal the value of CalendarDaily::CalFilterDateGlob right after we set the value of that field in our script.

Now create a relationship from your users table to billings based on the user and this new global date field. Then make a calc in the users table that sums the day's hours across this relationship: this is the calc that you'd show in the calendar's daily view beside the user's name in the users portal.

Hope that helps.