Don't understand the need for tables

[glenmac]

I'm going blind reading forums and FM help screens! I've got some dbs created in FMP5 and updated to FMP7 that need to change relationships. But, I just don't get how tables work.

I have databases for several pieces of equipment containing the elapsed time of use for different users. Each user has a unique name. I'd like to keep these dbs separate and then use a front end db to create summaries and subsummaries with breakfields based on budget numbers linked to each user (kept in a separate database). But, I keep getting hung up on how to get the tables to reference the other files. Or do the tables refer to related fields defined within the front end db?

This may sound really ignorant, but I've been pounding on this awhile and deadlines are looming.

Glen

[Genx]

Essentially...

Pre FM 7:

Each Table used to be one FM File -- you could build a 50 table solution only with 50 Files.

Post FM 7:

Each File can now contain multiple tables -- you can build a 50 table solution with one File, or really any number of files.

What you can also do with FM 7 is reference Files, so lets say you had one main Front End FM 7 File, you could reference all your data Files (File->Define...->File References) in this file.

Ah... Its a big topic so could take a while to go through, what are you having difficulty with in particular?

[glenmac]

Dear GenX

OK, I create a file reference in DB1 for DB2 then create a table with the lower left button in the Relationships window. I created a link between the 2 tables with userID field. Then created relationship Between DB1 and DB2 to bring in the supervisor ID from DB2, but the field in DB1 remains blank. Both are text fields.

I thought that I was pretty good with FMP, but the relationship issues from trying to integrate multiple dbs is confusing.

Eventually, I'd like to have 4 dbs with instances of equipment use, with a UserID, budgetID, date, time and elapsed time for each use instance. Then create summaries for each instrument with use by userID, budget number and department (from yet another db linked by budget number). Summaries could be nested, eg. Dept/budget/user.

Any suggestions on good examples?

BTW, I was in Melbourne 2 weeks ago, got to enjoy the rain.

Thanks,

glen

[Genx]

Hi Glen,

Okay, before we go anywhere, one critical thing here:

create a table with the lower left button in the Relationships window

This is not a table -- this is an occurance of a table - aka a Table Occurance. A table is an actual table located in the table tab and only exists throughout your database ONCE, in ONE file only. A table occurance on the other hand can exist once, ten times, a hundred times or even in extremely large systems thousands of times throughout a DB in multiple files.

It is essentially a "view" into a table. hmmm... I posted a small image that sort of explained this a while ago but can't seem to find the post.

Once you have that idea grasped things become a bit simpler...

Anyway, can you post an example zip of your scenario i'm struggling to grasp it well enough to explain what you've got to do.

As for the rain, i'll tell you what it's been icy windows every morning, and I'm Sick argh!!!

[glenmac]

Ah, right, that created a TO. But, I'm still stuck on the process of creating tables and relationships between them. Maybe I swing by the bookstore tomorrow. and pick up something with examples. This is my first FMP in quite awhile.

The basic need for my project seems much like a basic invoicing scenario. Users for a piece of equipment will provide a UserID and a BudgetID each time they reserve time. Some users will use more than one budgetID over any given billing period. I can easily compute elapsed time for each reserved time. I would like create billing/traffic reports with summary of hours used for each budgetID (like a sub-summary, with budgetID break field), with a list of summarized usage for each UserID associated for each BudgetID.

Sorry to hear, my Melbourne friends claim your winters can be worse for cold and damp than ours.

As for the rain, i'll tell you what it's been icy windows every morning, and I'm Sick argh!!!

Glen

[Genx]

Lol okay, fundamentals.

But, I'm still stuck on the process of creating tables and relationships between them.

This isn't exactly what you do... Lets say you have two tables, people and company.

People has:

CompanyID_FK

FirstName

LastName

Company has:

CompanyID_PK (make this a serial key).

CompanyName

Now... that's great, but as tables you can't really do anything at all with them.

So we'll make two table occurances - 1 for company called, "Company_Layout" and - 1 for people called "People_Related". Also add another table occurance called "Company_DUD" for company.

Relate the two table occurances by CompanyID_PK and CompanyID_FK. Double click the relationship and choose "Allow Creation of related records" on the people_related side.

Now, create a new layout, base it on your Company_Layout TABLE OCCURANCE. Note the significance of this. Whenever viewing this layout, you will now being viewing all related data from the context of the Company_Layout TO.

Okay, now you can add the CompanyName and CompanyID fields to the layout.

Next, Add a portal object based on the People_Related TABLE OCCURANCE. Add the fields firstname and lastname to the portal.

Okay, now go into browse mode and try it out.

... Next exercise, Create a layout based on Company _DUD -- and try and get related info -- you won't find any because we haven't related anything to that Table Occurance.

Essentially the concept is this:

1) A table occurance is only a representation of a table / a table alias.

2) The table occurance on which the layout you are on is based is the start of your "view" out into the rest of the database -- i.e. all your relationships will initiate relative to that TO

3) A TO can only see TO's related to it, so it stands to reason that a layout based on a TO can only see TO's related to it.

Lol, it's not that the rain isn't welcome, we only have 30% water supplies left -- sever drought, but it doesn't mean i have to like the freezing weather!

[glenmac]

Aha. thanks, that little exercise explained a lot. I think that I was missing the how the process worked. Now I need to play with this process awhile.

Glen