creating one table from multiple tables

[crebma]

Here's the issue. I have a planner database. I want it to pull info from the schedule database, banquet database, and entertainment database and display it on all on the calendar in the planner, very much like iCal. It already shows information from the banquet database, but that's all. I am using cc calendar's free edition and molding it to my needs. Is there a way I can get it to pull dates and information from all three databases and display it on one calendar? If you haven't seen it, the way it displays events is through portals. All I really did was replace the events tables inside the cc calendar with TOs from the Banquet Database.

Sorry for being so longwinded. Any thoughts?

[crebma]

I can never tell what it means when nobody replies. Either it's so easy no one is going to bother, or there's no possible way. I'll continue searching. :o

[coconutt2000]

Try offering money for a solution. ;-)

[T-Square]

Or giving us a little more info (like a Relationship graph or a clone of you file).

[coconutt2000]

Using the technique you've already used, you can create three layout sets in CC Calendar, one for each of the different databases.

If you want to merge the data in one view, you can create a third layout with portals for each of the different scheduling data, side by side. This could be kind of bulky, and it is far from elegant.

Now, I've never tried this, but maybe you should implement the default event tables, and then allow record creation from relationships to each of the different tables that create scheduling data. You'll have to use a prefix on your key to indicate which table created the schedule data, which means a calculated key that adds a prefix and suffix to prevent false matches and confusion.

This is a theory, but it should work, but I bet it'll take a lot of debugging.

[crebma]

Here is a relationship graph and a copy of all three files involved. I just want to know if it's even possible, or if there's a perhaps trick, which is why I offered no money.

Thoughts?

planner.zip

[crebma]

I was thinking about the things you mentioned, but they are, as you said, bulky and un-elegant. I figured there probably was no way to do what I want to do nicely, but I have only been developing for a little less than a year, so I thought I'd double check. But I am considering just giving in and putting the actual planner inside the databases I would like it to display. But I thought it would be nice to be able to view them all in one place. Perhaps I can figure out an applescript and just do it all in iCal. Thanks, though!!

[comment]

Calendar type displays are difficult to make even with one table. You should probably roll your schedule, banquets and entertainment into a single Events table. I am saying 'probably', because I can't see your file - it's password protected.

[coconutt2000]

Okay, how about going back to using CC Calendar's original event tables. Only difference is you'll add a third key to each table. This third key will note what planner table created the event. It will also be the third part of the relationship. On the planner side, the value will be a constant for each different planner type. Say, Banquet="banquet", Wedding="wedding", etc. On the calendar side, the value will be blank and will be filled in via the relationship when the new event is created.

The next step will be to relate each of the different planner type tables tables to the common events table, linking the new constant field in the planner tables to the new field in the event table, and the other two fields as usual.

I've actually only played with this idea, and it seems to work. Each of the three source tables can interact with the fourth table.

Make sure the ability to create records in the fourth table is turned on in each of the relationships.

Good luck.

[Fenton]

I would agree with comment. Just use (CC) one appointments table for events if you want to see all on one calendar. Label each event as a "type", so you can separate them with a filter, if needed. The label could be inserted (passed) from a "module."

[crebma]

Maybe I'm misunderstanding... Should I do a daily import from all three files into the one events table in CC? Or just do one and then set it up so that whenever I create a new record in one of the other databases it will add a record to the events table? That sounds feasible. Tricky, but not too tricky.

I apologize about the password protection. I always forget about that. The username is "admin" and the password is "brewth", if you're interested. I will give the second option a shot.

Thanks, guys!!

I will add: I usually offer to send brownies, if you'd like. :o

[coconutt2000]

Go with an common events table, and have the planner tables create event records via a relationship. Events for all your different types of events should appear together in your calendar.

The catch is that you have to add a "type" field to the events table that inherits its value through the relationship from a constant type field in each of the event type tables. Without that additional field I imagine the relationships will start to act mighty funny when you get two identical keys from two different event types.

Then again, I could be over thinking it. My experience with FM7/8 is still increasing with time, and I've read more about it than I've actually done stuff with it.

[Fenton]

Good catch by coconut. That is true that when you use a common "events" table you need to add a prefix (or type) to the IDs passed.

The basic idea is that an events or appts. table is a simple thing. It is mostly a date and a time, with an ID or 2 to tell you who/what belongs to it. So there is not really a need for separate tables, each with their own dates and times. Not if you want to view them all on 1 calendar.

You are thinking that the dates belong to the "banquet" or "entertainment", but it is just as logical to say that the banquet belongs to a particular date/time. In other words the date/times only exist in the Appts/Events table (in his calendar solution), but are visible in the Banquet or Entertainment. I think that's what coconut was saying, that you could create the date/time record for the calendar by typing into a related field, with "Allow creation of related records".

In fact, one could think of a "banquet" or "entertainment" as just something that happens at one certain date and time, therefore not needing a separate table for itself (independent of the events/appts table), only for its multiple "items" (if any). But having them as a separate table (or tables) allows the possibility of multiple date/times for one "event". In other words, there's more than one way to do it, but it's best to keep it as compact; don't separate things needlessly; it makes it hard to show them on the calendar.

[crebma]

Do you mean keep them all in the same database? I have been thinking about compiling all my databases into one database with a zillion tables and layouts, but that seems like too much data to store in one place. Although I suppose I could store it elsewhere and have it pull from there. But with fifteen databases up right now that are being used by my company, that will take some time. I understand the idea of having it create a record in the events table whenever one is created in one of the databases (banquets, schedules, entertainment), and that sounds like the best (only?) way to go with this. But if it seems like it's a good idea to compile all the databases into one huge solution, then I will gladly start with that. Ultimately, though, that's a different question, since coconut's idea will work whether they are all in one database or in separate databases. Maybe I'm over-thinking. I feel stoopid.

Thanks for your responses, they are extremely helpful! The offer for brownies still stands!

Amber

[comment]

Unless you have a good reason not to, I would suggest you keep all your tables in a single file.

I don't know what's the difference between a 'banquet' and an 'entertainment' (your file only shows banquets, and the Schedule file crashes my application). I am under the vague impression that these are events that your company is hired to do. If so, I would think they belong in the same table to begin with.

In any case, I don't think creating a record in one table whenever a one is created in another is a good way to go. If necessary, it should be the other way round: a record is always created in a central table (say Events). Then, if the record is of a certain type (say a banquet), and this type needs many fields that are inappropriate for the other types, you put those fields in a Banquets table with a one-to-one relationship to Events (based on EventID), and allow creation of records in Banquets. When you start filling those fields, the sub-record is automatically created.

[crebma]

My company is a brewpub, and so all these different files are things I've come up with to help us manage the information. For example, 'banquets' holds the information for all the banquets people will have with us. 'Schedule' is the employee schedule for all the different departments (I can't believe it crashes your application!! I made it in FMP8A!! EEK!!). I have a bunch of others, as well: entertainment, for a schedule of people who will be playing at the brewery, recipes, for... recipes, purchase orders, etc, etc, etc. I suppose the reason I have them all in seperate files is because it seems like it would easier to fix when something goes horribly wrong. Likewise, if someone manages to crash one file, they don't all crash. Obviously I was wrong with this assumption, and will get to work putting them all into one huge database.

I do, however, think it would be worthwhile to create a Banquet record and have a calendar record be created because of that, instead of the other way round. Although I suppose that if I were in the calendar and made a new record with the tag of banquet, I could then go to the banquet layout and fill details from there. Which is exactly what you said. hmmm.... I have much to think about on this.

I ought to add that before this job, my experience with database work was to the extent of... arrays in c++. I know that doesn't count, but I think you see what I mean. I thought I was getting it down pretty good, but apparently I've missed a huge point!!!

[coconutt2000]

Relational databases require a lot of abstraction. Looking at your other files, I'd have to say that you have a logical mind and a flair for design.

You're doing fine. Just take a break, have a few beers and come back to this problem after you're a bit sloshed. Abstraction always seems easier when you're little drunk.

If you haven't bought a book yet, I'd recommend either the Missing Manual, or the soon to be released Using Filemaker Pro 8, special edition. The latter being the latest in a great series of books on Filemaker Pro. Indispensable really.

(And think of files as merely containers for your tables. Where the tables are doesn't matter, so long as you set up the relationships properly. In fact, logically grouping and naming things is really just for human benefit. The database only cares to have the relationships properly attached and defined.)

[comment]

All I said is have a good reason for separation. Separation adds complexity, so make sure you get something in return. I can see why one would want to separate core business data (income/expenses) from management data (employee assignments, artists' booking, etc.) and maybe more - the interaction between these modules is minimal, and putting them together makes the relationship graph hard to manage.

So you haven't been necessarily wrong - just make sure you put a lot of thought into this. And I would design towards functionality, rather than away from crashes - a good backup plan is IMHO a better way to deal with those.

Back to your original question: now that I have managed to open your Schedule file (it didn't crash, just took such an awful time to load I thought the app gave up), I can see that my suggestion will not work.

I think you have two possible routes: either create more relationships from the calendar to your different tables, or go with the union table approach (one table that consolidates everything that has a date and a time to it).

Much depends on how you want to view the data: in the first option, the various data types will be displayed separately - each comes from a different relationship, so each needs a portal. In the second option, the various data types will be bundled together (at most, sorted by type).

Finally, bear in mind that no calendaring solution in Filemaker will equal the kind of polished display you get in iCal.

[comment]

Where the tables are doesn't matter, so long as you set up the relationships properly. In fact, logically grouping and naming things is really just for human benefit.

It does matter to me - but then I am only a human...

[crebma]

I knew I shouldn't have quit drinking!!!

I will look into that last FileMaker book you recommended. It will be a bit though, as I am a poor person (living in mid-michigan you don't get paid much to run it departments). I suppose it would work fine to combine all the files. It would just take a bit, but it would also make backup a bit easier.

[crebma]

Alright, so it's down to this: tabs, or record-creation. I suppose tabs could work, I guess there's no real reason to see everything happening at once, I just thought it might be nice. It seems like record-creation might get a little excessive, and if something changes somewhere, or someone cancels, or anything, that might cause problems.

I agree that nothing will be as beautiful as iCal, although iCal cannot grab my data dynamically either, thus giving me the same issues I have with option number two.

Thanks very much for everyone's input. I really appreciate it!

Amber

[comment]

iCal cannot grab my data dynamically

This is not necessarily true, esp. if your solution is served:

http://www.clevelandconsulting.com/cp-app/icalsubscribe/1

http://www.filemaker.fm/CDF/fm2ic/fmp2ical.html

http://myfmbutler.com/index.lasso?p=369

[crebma]

Unfortunately, I do not have Server. Just 8 advanced. We will need Server, soon, though. Right now there are two computers accessing the served databases, thus, no need. But soon, we will have 8-10. So we will need it!!