Pulling data into a report from multiple tables..

[agutleben]

Here is my issue:

I have 3 tables, let's say Clients, Client_Relatives, and Client_Past_Address. I want to create a report in the clients table that gives me all of the related relatives and all of the related past addresses in one report. I am unsure how to do this. Can anyone help?

Thanks in advance!

[faaslave]

Since you want to keep multiple addresses on everybody it might be best to have a table for addresses. Then you can have different kinds of addresses: present, last, dates maybe, I don't know how many you are talking about.

Then on your client table you can list these addresses in a portal, showing all of them, if that is what you want.

I take it that the relatives can also be clients? This would be a many to many relationship and would require a join table between them.

Is this some kind of geneology database? Do you have an example to upload?

I am sure that someone like -queue- or fenton can easily help.

Dave

[agutleben]

These were just examples.

Another way to explain is having Table A, Table B and Table C. I want to run a report from Table A that has all related data from Table B and Table C.

[Vaughan]

Aaron, I've found this to be the hardest thing to do. I don't know whether it's just hard in FMP or whether it's hard in all RDMS (I suspect it's hard in all) but afaik there is no easy way around it.

In one solution I saw the developers had one "data" file that stored all three kinds of records. It had many redundant fields but gee, the problem was solved. I kinda suspect that the data design of the database can make the issue disappear, or really bad.

Not really an answer, more like a recognition that you are not alone.

[CoZiMan]

One thing I noticed recently is that you can import from one table to another IN THE SAME FILE !

This means you can build records and calcs in a table and then import them into another table as completed records with an "import" script.

Sort of like a "lookup" but you could script this for completed forms or reports.

Just a thought.

This may actually be the simplest way to get past the 11 page single record printing bug.

Hmmmmm.

[hayesk]

I would make a generic reporting table with generic fields - import from the others into that and generate the report from that table.

[transpower]

This is more of a job for SQL: you can query multiple tables. On Windows, you would use MSQUERY. Not sure what you would use on the Mac, but there's got to be something out there.

I would agree with Vaughan, though, that you should really carefully think through your database structure before proceeding.

[agutleben]

Thanks for everyones responses. This solution is pretty far along, so rethinking my design is out of the question, but there has got to be a way to pull normalized data back in for reporting. I thought of exporting / importing into another table, but I have multiple locations coming into this solution via a WAN connection, and I do not want to pull an import if I do not need to. I thought about a join table, but I am not very familiar with setting that up. Confused, but the light will come on eventually. It always does. Thanks again, and if any other ideas should come along, I am open.

[-Queue-]

If you are concerned about simultaneous imports conflicting with each other, you could always use an auto-enter field containing the user's name. Then once the imports are complete, perform a find for the name or use a relationship from an unstored calculation of Get(UserName) to it and use Go to Related Record [show only related; yourrelationship]. The found set will then only contain the current user's records and multiple users can run the report simultaneously without affecting each other's records.

[Fenton]

By "reporting" you mean you want to Print this? It makes a difference, because there's another way to do this with 2 portals, but portals print like crap.

I don't see that this is terribly difficult to do, especially if you're willing to accept certain assumptions.

If there is an overlap of relatives, that is, if a "relative" can belong to more than 1 client, then you either must have a join table, between Relatives and Clients; or that relative must have more than 1 record in relatives.

Similarly, if more than 1 Client can live (or has lived) at the same address as another, then either you need a join table (which seems silly); or multiple entries for that address, one for each client that's lived there.

Actually one 1 of two above has to have multiples; addresses (which makes more sense anyway). The other, the Relatives, can be obtained as ValueListItems.

The limitation of ValueListItems is that the targeted field must be just that, 1 field. This would work fine for Relatives; using a First & " " & Last (or more) calculation. It has to be an indexed result.

The report would be in the Addresses table. The layout has a Subsummary by client; sorted by client. Also in the Subsummary is a calculation field, ValueListItems of a filtered value list. The relationship is from the Client ID (foreign key) back to the Relatives.

(P.S. I used a join table for Relatives in the example file. It's easy in 7, with "allow creation of related records". But it's not needed if Clients don't share relatives.)

ClientsRelatives.zip

[agutleben]

Fenton,

The attachment you gave was excellent, and will solve my issue. I had never thought of using a value list in that fashion, but it will do the trick.

Thank you for your time and wonderful explanation.

If anyone else views this post, please download the attachment that Fenton posted.