Jump to content
Sign in to follow this  
Answers

Many to Many relationship?

Recommended Posts

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

Share this post


Link to post
Share on other sites

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/

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites
Posted (edited)

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 by Steve Martino
additional info

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By plunkettphoto
      The best way to pose the question is to fully describe what I want to do.  I have an application that documents board meetings for an association.  The base table for the layout is "Meetings".  There is a related table named "Attendance" and another related table named "Trustees".  On the layout is a portal to the Attendance table with Trustees Names and a checkbox to indicate if they are present or not.  What I want to do is when they create a record in Meetings for a new meeting, I want to populate the Attendance table with records for each Trustee for the current meeting.  Then all they must do is, for each trustee, indicate if they are present or not.
      So how do I read each record in the Trustee table and create a record for the new meeting in the Attendance table?
      thanks,
       
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.