sort and compare

[Tdoren]

Thanks in advance I have learned a lot from all of you at this site and hope to be of assistance to someone else someday.

 

I have a fmp 8.5 database that has a table for equipment and then related tables for tests that are performed yearly on each piece of equipment. My problem is when I return the next year the equipment tested is not always the same, some equipment no longer exists and some have been added. After testing I would like to be able to sort and print a list of current equipment that was tested and a list of equipment that was not tested for that customer. The relationship between the customer and the equipment is one to many through customer number. The relationship between the equipment and the tests are one to many through serial number. Each test has a field called date tested and is an autofill via date created field which could be used to sort but I am not sure how to use this to compare to the equipment list.

Can someone please at least give me some direction

[touchMe]

First thing you might want to think about is adding an equipment status field, this could hold information such as, equipment exists, equipment replaced, equipment dissapeared in a big blue puff of smoke... or whatever

 

This might be helpful in your find routine, when you grab all the records for one customer or as a list for all or a group of cutomers, you don't want to find records for equipment that no longer exists do ya?

 

How does your DB 'know' that a piece of equipment has gone, been replaced or whatever? - how do you update the status field, I dunno without knowing some more... (perhaps an auto calc would be nice, but what are the conditions that define equip has gone etc.?)

 

If no test has been performed on equipment (in yr), does that mean it no longer exists? - or could it mean it was missed or test was not required? - not sure what you mean by 'compare'

 

If all equipment (ever) is sorted by customerNumber but only to include those entries which have received one or more tests in year(date tested), or equipment defined by status, then you could compare that to the omitted list (again sorted by customerNumber) which would contain equipment with no tests, or status replaced etc.,

 

Just to get you started...

[Tdoren]

Hi touchMe,

 

Thank you for yor reply. Actually I came up with a solution while I was writing my question. I added a flag field which worked great.

 

Thank you for your input

tdorensmiley-wink