So far, it's unclear to me what goes on a job sheet in your solution. Can you sketch-out how it should appear and possibly what you think the E-R diagram, showing relationships between the two or three tables, should look like?
The tables in basic form would be:
CUSTOMERS TABLE APPLIANCE TABLE JOBSHEET
address make job date
phone model job description
Our 20 year old plus database which is no longer supported! Allows us to search a customer, then list their appliances and print a job sheet with the Customer and appliance information along with call times etc. I know filemaker can do it but just can't find a way round it!
SORRY IT WASNT TO SUPPOSED TO LOOK LIKE THAT!!
Details are important.
Do you want to select/find a customer and put all their appliances on the job sheet or do you want to pick from a list of appliances owned by the customer in order to add one or more to your job sheet?
Might a jobsheet list more than one appliance for that matter?
Do you need help finding the customer or just help getting that list of appliances?
A Job sheet is like a work order? If so, it's more like an Invoice where you look up the customer, select an appliance, and populate date, description and time.
Can a work order turn into an invoice? If you need it to, then you will also need to create a table of products and services that you can add as line items. When the job is finished it's a full invoice.
If your service person has this with them on an iPad, you can have the customer sign it, email it to them (or portable printer) and/or accept payment via credit cards with something like GoPayment.
For me, in the HVAC business, I print out the equipment info onto paper-listing all the components of the equipment (these components are also tracked for inventory). This is done from the Customers Equipment table.
Then on an iPad, when I'm finished, I build the invoice (Invoice Table), with the customers name and all pertinent info.
I don't see the need for a Job sheet, but that could fall more into a dispatch sheet attached to a scheduling program.
Each job sheet will be for one appliance, so I will need to see a list of the customers appliances and choose one appliance for that specific job!
ie: A customer has 20 Boilers in Boiler rooms 1 to 20! if the Boiler has a fault in Boiler room 18 i would like to choose from the list of the 20 Boiler rooms and populate that to a job sheet!
Many Thanks for your Help
Hi and many Thanks for your remarks, you are exactly where I would like to be with regards to technology!
The reason I have chosen filemaker is that it will give me the flexibility to have our engineers do exactly what you have mentioned! We have a Database which is 20 years plus and has worked exceptionally well, but it is totally non customisable! I purchased an off the shelf service solution 18 months ago, which promised remote user compatibility for the on site engineers, it was all singing and dancing and was going to make me a fortune!! Unfortunately the office staff will still not use it as it is so un user friendly and the engineers hated it! So we still use paper job sheets! :-(
I am hoping that I can develop an app with similar data import to our existing database, so the office staff are happy and gradually bring in improvements !! Well thats the plan!!
What you are describing is often handled with a conditional value list where you get a list of values (appliances) controlled by the value selected in a different field (customer ID). In this case, that gives you a list of all appliances for a given customer.
I've created two teaching files on the subject of selecting a value from a list. The first one deals with conditional value lists, the second deals with alternatives to the typical value list or conditional value list. Both come with detailed info on how each working example is set up so you can read about them and then take the file apart to see how the example was put together: