summary report(?) of fields from multiple tables

[titanium]

Hi, Im not sure what i am trying to do is called, but I certainly appreciate any pointers you can offer, or link to other posts that may have delt with the same issue.

I have multiple tables containing different types of data about a customers various systems. the data is collected/entered in form of an inspection which is dated upon creation then we print the inspection report. When the report is printed, the inspection is marked as printed in a field called "PrintedYN" They default to "N" then are changed to "Y" when the report is created.

What I'd like to do is create a report(?)/Layout of some sort(?) that tells me which customer has data not printed. So, ideally, the layout has the customer name, and the date of the inspection which has not yet been printed. I can then click on that and go to the related record and generate my report. (thats the easy part for sure)

I feel that I need to have a script which does "If PrintedYN=N, display customer name"...but not sure a, if that is the right approach, b, where I put that...

Im not sure the best way to go about this so not really sure where to start looking.

Thanks in advance for any help you can offer !

[Fitch]

Sounds like a basic Find for "N" would do it, do you need help scripting that?

[titanium]

Fitch, Thanks for the reply. That does sound like it would work... I think i should be able to manage it, but my first thought is that I am trying to find "N" from several tables all that relate to one customer. So the end will be CUSTOMER #1 has System 1 ="N", System #2 ="N", Customer #2 has System #2="N", etc. Ill work on that and see what happens... Thanks again for the tip and any other thoughts you may have in the mean time.

[Fitch]

System 1 ="N", System #2 ="N", that sounds like separate records, not separate tables. Are there actually other related tables for the customer?

The reason I ask is that if there's only one related table, a fairly easy way to find customers with "N" would be to do the search in the "System" table, and then find the related contacts via Go to related record (GTRR) using the "Match all records" option.

[titanium]

Hello

I should have been more clear. Rather than System #1 it should state Sytem type #1. And each system type has it's own table.

I have played around witht the find for N and so far so good because the tables all have pre-exaisting relationships that allow me to search each system type and display by customer name.

I really appreciate your insight. It's helped me get started. I think I'm on the right track now tho. I'll post again and let you know how it goes or if I run into any problems!

[bcooney]

SystemTypes could be one table with a record for each type, and each System would get a SystemTypeID. I see the need from your first post of a Customer Table with related Inspections. Each Inspection would have a _kF_SystemID. The Systems table would have a _kF_CustID.

Your report would be from the Inspections table, finding all Inspections not printed (I'd have a DatePrinted field and find for recs with an empty DatePrinted).

Your data model is not correct. I suggest asking us to help you with your structure, before you paint yourself in a corner.

[titanium]

bcooney,

Thanks for your thoughts. I know my data structure is incorrect.When i first started the application it was the first time id used FM. I had hired a developer who turned out to pretty much screw me over and this is what I was left with. after hiring another to get it working things do what we need now, but make things much more difficulty to change as new ideas come up. If I had to start again, Id know a lot more now, and certainly go about the whole process differently, but what we have works and now i have to work with what Ive got.

Hind sight is 20-20!- live and learn :)