need help with relation

[ColdSanta]

I have a table called Inspection. It stores suite inspection information from related tables (Property, Suite, Staff) and user input. One Suite can have many Inspections.

Now, if I do next inspection for the same Suite I need to see previouse inspection date on the form. How can I do this?

Thanks

pm_test.zip

[aldipalo]

Hi Santa:

I'm only doing this cause I want lots of presents this Xmas.

I looked at your relationship graph and it was certainly well organized and quite extensive. However, you didn't relate all the tables together. So, I made a separate, very simple, relationship for you to look at.

Also, all of your layouts are tables, so, I made a layout so you can view all data on one screen.

I added a portal to the layout so you can add inspections to each suite on the same screen.

Let me know if this is at all helpful. I believe it gives you a running start. If you have any other questions let me know. I'm sure others with a lot more experience than I will give you other pointers.

Al

pm_test_Copy.zip

[ColdSanta]

thanks

But how do I put previouse inspection date into a new inspection

[aldipalo]

All of your inspections for this property and this suite will show in the portal. You can then sort in Ascending or descending order.

I added a date field to the portal so all you have to do is enter the date and the employee (I'm sorry I assumed you wanted to know who did the inspection)the record is automatically generated.

If you go thru the records you will see this.

HTH

Al

pm_test_Copy2.zip

[ColdSanta]

When I create a new Inspection I will enter the Inspection date (this is what you showed me)

But what I need is to have another field that will store the previous inspection date for the same suite. (of cause if the suite had any inspections before)

So for One Inspection Record I will have ”Inspection Date”, “Previous Inspection Date”, “Suite #”, “Tenant” and so on…

[LaRetta]

if I do next inspection for the same Suite I need to see previouse inspection date on the form. How can I do this?

Well ... you asked the question three times so I think you deserve an answer to it. I believe this will get you there:

Create a new table occurrence of your Inspection table called Suite Last Inspected. Join as follows:

Inspections::SuiteNo = Suite Last Inspected::SuiteNo

AND

Inspections::InspectionDate < Suite Last Inspected::InspectionDate

It does not appear your Inspection dates are entered in (creation) order so a sort may be required. You want your Inspection dates sorted in descending order by their date. If you need to sort, do so at the relationship level. While in your relationship dialog box, check to sort Suite Last Inspected (below) by InspectionDate (descending). Now to complete your prev_ins_date in Inspections. You will want to use a Lookup so the prior Inspection date implants in your table (I think). In this way, as an employee looks back through the inspections, it will be quickly clear how much time went by since the prior inspection. Your Lookup will be:

Starting with table: Inspection

Lookup from Related table: Suite Last Inspected

Copy value from field: date *

* A field name should never be the same as a function. I suggest changing it to InspectionDate or InspDate. Otherwise, you AND FM could get confused in calculations...

If you need further assistance, let us know.

LaRetta

[ColdSanta]

it does not work for me

it looks up the very firs inspection date, not last

Edited March 29, 2006 by Guest

[Oldfogey]

I'd suggest a self-join as LaRetta suggested but leave out the date - ie just self-join on Suite No.

Define your Date_Last_Inspection with Auto-enter calculation of

Max(Last_Inspection::Inspecton_Date)

and flag 'do not replace...'.

[LaRetta]

I suspect that you didn't sort the relationship by Inspection Date (descending). It would be quicker (I believe) than using the Max() function. Can you post your file?

Here's an example (attached). Auto-Enter can also be used but Lookups were already in the file so I knew ColdSanta would be familiar with using them ...

Keep in mind that it won't look up the date (and can't) until the suite no. is entered (and committed). Even if you used an unstored calc, it won't give you the prior entry until you've entered the suite no.

pm_testREV.zip