Job's and instances of jobs

[jdenver]

I am stuck on how to move forward and feel I might be taking the wrong approach.

I am creating a database for my one-man business - service engineer.

I have created a customers table and a jobs table, linked the two via id and foreign_id with a one-to-many relationship and all works well, the id’s are created using the UUID method.

Every time I create a new job, a new serial number is created using the auto-enter serial number in the field called ‘jod_no’ in the jobs table.

This is all ok if its just one call out and the jobs fixed and dealt with on the same day.

But sometimes I need to create two or more jobs (and I refer to them as ‘instances’ of the first job) ie First job would be a callout and diagnose, 2nd job would be to order parts and the 3rd job would be to fit the parts. I look at this as 1 job with 2 instances.

In order to do this so far I have added to the jobs table a ‘instance’ field which I wanted to display a 1 or 2 etc. depending on the number of instances created for that particular job, and a ‘status’ field that I intended to use a check box to say when the job is closed/finished.

I have created two buttons on the layout one for a ‘New job’ and one for a ‘New instance’ .

What is happening is when creating a new instance, a new job number is being created (because this is the auto-entry serial number field) when really what I would like is only a job number created on a new job and then that job number copied over to the new instance when a new instance is created.

Would be grateful for advice on where I am going wrong or a different approach to this.

Thanks in anticipation.

[Steve Martino]

I think you may need an instances table, and it can show as a portal in your jobs table.  Then you can have all the details of what is going on in that 'instance'.

Think of a job as an invoice, and an 'instance' as a line item.

You can even add another table, mimicking products, for parts, or tasks to track those together or individually.  Then you may need another table of Vendors.

You're heading down the rabbit hole!

[jdenver]

Thanks Steve, I understand what your saying regarding invoices, but creating another table I feel would be a waste as the fields I want are in the job table. Ive worked it another way now but thanks for your advice.