Finding Madness

[Moosh]

Hi all!

I have been struggling for DAYS with writing a find script to do what I believe should be fairly simple.

I’m building a property management database. I have two tables, Tenant and Lease. These two are related via the Tenant ID field, present in both tables.

When in the Tenant layout, I can press “create lease” which creates a new record in the Lease table and copies over base tenant information including the Tenant ID.

What I want to do is from the Tenant layout – create a button that says ‘view lease” such that it references the tenant ID number in the tenant table, goes to the Lease Layout page and finds the record that has the tenant ID that matches the tenant ID from the Tenant layout.

When I script the Perform Find, I want to find where Tenant::Tenant ID = Lease: Tenant ID….

Any help will be very much appreciated!!

If I am not being clear, please let me know.

Thank you!

[Razumovsky]

Hi Moosh,

The problem is that you are not exploiting the relational features of Filemaker. Forget copy and paste and scripts for now and read up on 'designing your database' in the help file.

Basically, it sounds like you need to connect your two table occurences in the relational graph TenantID=TenantID and select 'allow creation of related records through this relationship' in the box.

From your Tenant table layout, create a portal based on Leases. Now you can create new leases that are directly related to their tenant through the portal by just typing in the fields.

On the Tenant table layout, you can just put fields Lease::LeaseData all over the place to view and edit related tenant data over the relationship.

-Raz

[John Mark Osborne]

Sounds like you want to use the Go to Related Record script step. It will locate the related record and display it for you with a single script step. No need for a find script.

[Razumovsky]

Apologies, I did a poor job reading through your first post. You are in better shape than I thought - John is on the ball.

[Tricky]

This is true, but your remarks about the relational features of Filemaker might also be worth thinking about for Moosh.

[Moosh]

Thanks for the leads and tips.

I thought I was using the relational features -- as I said, I have 2 tables that are linked through the tenant ID.

I tried the go to related layout which says it can jump to a layout that's based on the same table. If this is all I can do with this function, then it doesn't solve my problem as I'm trying to go from the Tenant table to the Lease table.

Thank you!

[Fenton]

"I tried the go to related layout which says it can jump to a layout that's based on the same table"

There is some ambiguity here. The phrase "based on the same table" means "based on the same table as the target base table* of the relationship", NOT the table where you started.

One wonders what exactly a Lease is in this case, if it is actually the property, and whether a tenant can have more than 1 lease. But if the Tenant ID is in the Lease table, you can certainly go to it from a Tenant record (providing the relationship is correct; but you know that).

*I say "base table" to distinguish from "table occurrence" (those boxes on the Relationship Graph). Because the final layout you can land on is one of the few places in FileMaker where it is the base table that matters, not the particular table occurrence, during an essentially relational operation. Which is good, as it frees you to move around quite a bit more.