Compiling records from multiple tables?

[Timothy Moser]

The database is for livestock management, specifically for goats. For the present, all the tables I am concerned about are related to the Goat table, in which each record represents a different goat. Each of these related tables gives details about a different sort of thing that happens to a goat; for example, each record in the Symptoms table has a date, a Goat ID, and a symptom, and each record in the Weight table has a date, a Goat ID, and a weight.

Most of my users are probably not used to entering these different things in different tables. They would rather have a single table for each goat with the dates on the left, each of the other fields representing one of the tables previously described. It would be as if records from all the other tables were dropped into this one and sorted by date, each table getting its own field except that all of the dates are in the same field. Then the users could modify either records in the individual tables or records in the big, compiled table, and they would update each other.

I know it does not make much sense, but that is really the sort of thing I am looking for: one table that represents a bunch of different tables, updating them and being updated by them. I have tried several different things and I cannot satisfy myself with anything. There is probably no way to do what I want in FileMaker, but any good suggestions would be immensely appreciated.

Thanks,

-Timothy Moser

[comment]

It's sort of possible, but not easy to implement. Roughly, you'd need a table for the viewed dates and a relationship from there to each of the sub-tables, based on a global gGoatID and the date. A portal to this table placed on a layout of Goats could give you the arrangement you're looking for. You'll need to use a script trigger to update the gGoatID field every time you move to another record.

Note that if there are, for example, two symptom records for the same date, only one of them will be shown.

[Timothy Moser]

I thought about having a table just for dates, but I was not sure how to have FileMaker automatically create all the records. Then, of course, there is the problem that you mentioned: when two things happen to the same goat on the same date. The idea of using a global field that changes sounds like it could sort of work, but I suppose everything would mess up if two people were using the database at the same time.

Still thinking…

[comment]

how to have FileMaker automatically create all the records.

There is no need to. You only need to create - once - the number of records you intend to view at a time. This is basically a calendar - see:

http://fmforums.com/forum/showtopic.php?tid/176396

Then, of course, there is the problem that you mentioned: when two things happen to the same goat on the same date.

True, but you will have this issue with any arrangement where each row is a date.

The idea of using a global field that changes sounds like it could sort of work, but I suppose everything would mess up if two people were using the database at the same time.

I don't see why.

[Timothy Moser]

What I was thinking was that, for example, if one user goes to the record for Goat 04X106HBK4 and another user immediately goes to the record for Goat 07X123WMB3, and the script changes the global field to 07X123WMB3, that would affect the data entry of the first user. But I guess the global field only affects which records are displayed in the portal when the record is first accessed and that any modification to that data does not depend on the global field.

Thanks for the feedback; even if I don't do it exactly this way, it helps me think about different ways to deal with it.

[comment]

Global fields can hold a distinct value for each user (or rather session) - so each user can view their own set of related records.

[Timothy Moser]

FileMaker Pro 10 Help says, "If a file is shared, some calculations that include global fields are evaluated on the host instead of on the client. …a record access calculation to determine the rows to display in a portal or the values to display in a related value list will occur on the host if the record access calculation includes a global field." If the portal does change while a client is using it, will that effect the way his data is used according to the design you outlined?

By the way, I just set the database up the way you suggested and it works pretty well, at least for a single user. I might just have to work around inevitable problem of only a single line per date.

[comment]

I am not sure what the Help says (it seems a bit out of context). If a portal is keyed off a global field, each user will see their own related set, based on THEIR value in the global.

[agaperrk]

Set up a portal based on the Goat Id then just Show the portal on the goat record.