Complex search result

[Seeker4]

I am uncertain how to get a particular search result.  I am creating a Home Owner Association solution, which has:

 

HOA table with one record

 

Lots table with one record per lot (lots have a lot number)

 

Home Owners table with current and previous (timeDeleted not null) home owners.  There is a unique homeowner ID per record. Linked to Lots table by lot number.

 

Addresses table with current and deleted (timeDeleted not null) addresses.  Linked to home owners table by homeowner ID.  Has a Type field with values such as Owner, Billing,and others.  And has a PMA flag (0 or 1).

 

I want to create a search that gives me a list of addresses that meets these criteria:

 

If a Billing address exists, use it.  If no billing record, then I want the Owner address record that has PMA=1 (there will be exactly one such record per homeowner).  Deleted address records are to be excluded, as are address records linked to deleted home owners.

 

The goal of this search is to give me a list, one record per lot's current homeowner, showing where HOA dues bill is to be sent.

 

I can see ways to get this result using two searches and an intermediate table, but am wondering if there is a better way.

 

Thanks for any suggestions!

 

 

[ggt667]

Read up on: case( ) and choose()?

 

choose( PMA; "PMA is 0";  "PMA is 1" )

 

I believe I would also make a flag, but not as a calculation: I'd use this method: http://wethecomputerabusersamongst.blogspot.com/2012/11/filemaker-syncing-data-across-timezones.html to provoke the update of the flag only when the record is edited.

[Ocean West]

Since sending out bills is important and you need to know if every one of the Homeowners is represented,

 

For something like this I may create a new Join table that is "billing" and it is a record that is the LOT / HOMEOWNER  / and ADDRESS

 

the relationship may be just lot / homeowner (the uuid for these entities)  and then when you click a button on the address portal it sets the uuid for the address for this information. 

 

The end result is a table of records for each homeowner / lot  with the correct mailing address.

 

part of your data entry you could use triggers or conditional formatting that alert you that this homeowner doesn't have a valid billing address

[Seeker4]

Stephen,

 

Thank you.  I've done something along those lines but am sure there is a better way or more direct way to accomplish this.

 

What I did was search the Address records for addresses that were Owner addresses with PMA flag set and neither address nor owner were deleted.  I exported the address and homeowner uids to an Excel file.  I repeated the search looking for BillTo records and exported that to another Excel file.

 

Then I went to my new Billing file, which has fields only for address and homeowner uids, and first imported the Owner address set I had just exported.  Then I imported the BillTo set, this time matching on homeowner id and updating the address id.  That gave me a billing table with exactly one entry per homeowner with the desired address uid.

 

Then I export the billing file out to Excel with about 20 columns of data.  I use calculated fields to pull data from related records so that I have pretty column headings in the Excel worksheet.  (The Excel worksheet is what goes to the accountant for actual billing.)

 

It seemed obnoxious to me to save the records to Excel and import them again, but I didn't see how to do this directly in FMP (at least without a loop, which would have been possible but probably even less desirable).  I'd appreciate any suggestion on any more straightforward way to accomplish this step.  (I haven't used FMP since v4 perhaps 15 years ago, so am thoroughly out of practice and am probably missing something simple and obvious.)

 

I suppose I could maintain the billing table with triggers but that might require some slightly tricky scripts to accommodate various changes to homeowner, addresses, etc. I need this billing table only twice a year, so it seemed safer (less code = fewer bugs) to create the table on demand rather than to maintain it.

 

I already have to deal with data errors as I am using FMP to do what is now done with Excel and that worksheet has lots of inconsistencies and little errors.  I also know too well that my scripts can introduce errors.  So I wrote database validation scripts that check that the data is consistent and flag records with issues.  These seem to be working well.

[eos]

Wouldn't it be easier to use a foreign address ID field in the Owners table and maintain that, according to the rules you now use to find addresses? Then all you need to do is find active home owners, GTRR to the Addresses table and print the list.

Read up on: case( ) and choose()?

 

choose( PMA; "PMA is 0";  "PMA is 1" )

 

I don't see how that helps here, but anyway: wouldn't

"PMA is " & PMA

be more to the point?