[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

How Do I create a database with relationship?

I am a newbie in Filemaker Pro and trying to create a database for onsite service call which comprises of the following tables ( Service Call, Report Summary, Report Details).
In Service Call table, it comprises of the following fields (Invoice Number, Report Number, Service Date, Requested By, Client, Fault Reported, Time In, Time Out, Retification, Time Taken, Amount Billed, Billing Month, Report Date)

In Report Summary table, it comprises of the following fields(Invoice Number, Report Number, Service Date, Client, Amount Billed, Billing Month, Report Date, Billing Month)

In Report Details table, it comprises of the following fields (Invoice Number, Report Number, Service Date, Requested By, Client, Fault Reported, Time In, Time Out, Retification, Time Taken)

My question is what should I do to make it as such that when I input a record in Service Call table, this record will also be automatically entered in Report Details and Report Summary? I tried using relationship, but it can't work. Please advise what function should I use? Sorry for the lenghty question. Thank you.
0
SidneyChng
Asked:
SidneyChng
  • 3
  • 2
1 Solution
 
jvaldesCommented:
You should not be storing the same data in multiple tables. Instead you may want to store client specific information in a client table, you should create an invoice header table where you can store all the invoice information that is not line dependent and then you should create an invoice line table to store all the line entries. This approach provides the maximum flexibility when it comes to using summary fields in Filemaker.

 If you have a report structure where you keep track of the event information that is not on the invoice, then you should consider having a table for that.

I would use a customer number to index the client table, invoice number to index the invoice header, Invoice number and line number for the line data, line number should index the report table.

Provide a copy of what you have so we can help
0
 
SidneyChngAuthor Commented:
Hi jvaldes
Thank you very much for your prompt response. I have attached a copy of Report Summary and Report Details in PDF format for your reference. They are at https://filedb.experts-exchange.com/incoming/ee-stuff/5022-Report-Details.pdfhttps://filedb.experts-exchange.com/incoming/ee-stuff/5054-Chling-Services.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/5023-Report-Summary.pdf
 ready for download. Thank you very much for your advise.
0
 
jvaldesCommented:
I was refering to the database so I can provide guidance on how to address the problem
0
 
SidneyChngAuthor Commented:
Hi jvaldes

I have uploaded the databse at https://filedb.experts-exchange.com/incoming/ee-stuff/5054-Chling-Services.zip. Please provide guidance. Thank you
0
 
jvaldesCommented:
Go to the File=>Manage=>Database

When the screen opens select the Relationships button on the top, then select in tyhe table you want to relate the field that you want to use as a key (a tie in to another table like invoice number in your tables) then drag the field to the other table and field that is the same key in the other table.

The default relationship that is created is the equal relationship. If you would like to create a more complex relationship you can drag another field combination or you can edit the relationship by double clicking the square box between the relationship line.

let me know if I can help further
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now