Many to Many relationship?

[Answers]

I have a database that has the tables customers, estimates and inspections. (there are other tables as well) Anyway i currently have a layout per customer which has two portals, one that displays the estimates per customer, one that displays the inspections.When you click on one it takes you to the details of that "form". It works nicely but now they want to see one portal with the estimates & inspections listed in one portal. I thought what I would need is a join table but somehow I have become baffled. All three tables have the customer id. Inspections & Estimates have appliance ids as well. The end result is to view by customer all of the estimates and inspections per appliance id. I can make a sub summary report but they would like:

Customer

1. Appliance 123
   Inspection
   Estimate

Any suggestions would be greatly appreciated. I might need to be more descriptive.

Caroline

[Steve Martino]

Hey Caroline!  Long time no see.

Trying to understand the structure better.

Based on your description, where does "1.  Appliance123" come from?  Is that a Job, Location?  IOW, can the Customer have more than one Inspection/Estimate(/Job)?

Does each 'job' get only one inspection and only one estimate? Meaning will 'Appliance 123 get another Inspection/Estimate or will that be another record?

Is there a conclusion to the inspection or estimate that turns into work (invoice?)

I think one solution would be to use a virtual list report (or JSON) to gather the data in one spot for display.  This leaves you the flexibility to report by Customer, Inspections (or specific Inspectors if applicable), or Estimates individually.

The other options, probably not good, would be to flatten the file, then you lose the ability to report in more detail.

Here's a starting point for VL and JSON:

https://filemakerhacks.com/2018/06/29/virtual-list-reporting-with-json-arrays/

[Answers]

Hey Steve,

I was wondering if you would have some insight. How are you?

 I'll explain better ('splain it to me Lucy, LOL)

So the customer id is actually a property location (there could be more than one owner of a property over time, that is taken care of). So one property location can have many inspections and estimates. And one property could have more than one appliance (we are talking fireplaces and chimneys, the great northeast, you hear me). Previously everything was "driven" by customer id but easy enough to turn it into a property id.  And to answer a question you had, the estimate turns into an invoice but not in Filemaker by sending the total amount to quickbooks, basically posting totals. Filemaker creates a work order, contract and the details.

The hierarchy is, Property address- appliance- estimates and inspections.1- many-many.  Fireplace 1 could have an inspection and estimate every year. Fireplace 2 as well. Hence, I could search by the property address and appliance (i.e. living room fireplace) and see the history of the inspections and estimates. And right now I could search by property and appliance, but they would like a neat orderly format that doesn't require a search. Perhaps I need to create a table with appliance & property, (I had some ideas)...... no still the same problem listing all estimates & inspections (since they are in 2 tables, am I wrong?)

Okay enough babbling and thinking out loud. I will check out the link you sent.

And you are correct, there will be no flattening this file, there are much more pieces to this puzzle.

 

[Steve Martino]

I'm in the northeast and I hear ya (had enough of winter).

Yeah I think virtual list would be the way to go. Maybe a button that opens up a popover so when a user picks 'Appliance 1' the popover would show the VL like a mini sub-summary:

Appliance 1

   Date 1

     Estimate

     Inspections

   Date 2

     Estimate

     Inspections

etc.

There's plenty of things about all types of virtual lists on FileMaker hacks, and links to other referenced material.  For me, after some head banging, that was the easiest to implement.

The other thing I have been playing with is Data Tables by Jeremy Brown to create reports in a web viewer.

https://www.geistinteractive.com/2018/08/24/using-datatables-in-filemaker/

https://www.geistinteractive.com/2018/07/09/use-javascript-without-knowing-javascript/

There's a lot to digest so I don't know how much work time you can dedicate to it.

But with the above sample, every time you change an Appliance record or its related Estimate/Inspection, you could trigger a script to update the compiled JSON into an array, and it would display instantly in the web viewer.  Even a report showing all the appliances for one customer, and their related estimates/inspections would render instantly in the web viewer.

Edited March 21, 2019 by Steve Martino
additional info

[Answers]

I did look at the VL and download some sample files. And yes a lot to digest and I totally get the head banging, trying to decide the best solution to the problem and for me at times hesitant because it is a lot to digest!

 I think you are right, the VL is the right way to go and I like the web viewer idea with a popover button. I will make the time, this customer I've been working with really loves their database (they did everything manually 18 months ago) and so it has been a real time saver. So the extra effort is well worth it...

In the mean time I made tab panels on the property layout with each tab being an appliance 1, 2,3 etc.. filtered an inspections portal and the estimates portal on each per appliance number pertaining to that tab. This buys me time.

Next I'm going to tackle the VL so they can have  their information compiled. Thank you so much for pointing me in the right direction.