Relationships and Portal Problem

[Raater]

Hi Guys

You have all been really helpful with my previous posts and my job management system is working well but I have hit a massive sticking point and I hope you can help - or I'm in the s*&t!!

I have done a series of finds and constrain finds to find a set of records for a particular customer for a particular month, with a value for invoicing etc. I now want to show all these jobs on one invoice and then total them up and add VAT etc. But I can't seem to get it to work and I'm not sure what relationship to setup? When I add a portal to the invoice layout it just shows one record.

I have managed to set up the invoicing for other jobs and customer where they have one invoice for each job number but I can't get this multi job invoice to work. For the individual invoices I use the a unique job number as the key to pull in the client details and all the job information but as this new invoice type will have lots of jobs I can't use this. Also I don't want to include all the jobs for the customer so I can't use their account number as a key.

Hope this makes sense ans someone can supply a straight forward answer before I go bald from pulling my hair out!!!

Many many thanks for any help in advance...

Rich

[Phillip Holmes]

Hi

Can you attached the file in zip or rar format as it is near impossible to think this kind of question through until you see the actual DB.

Regards

Phillip

[Fenton]

I think the answer is to put the Invoice ID into each Job. Hence a Job becomes effectively an invoice line item. It sounds as if the way you were doing it before was to put the Job ID into the Invoice, which would work for 1 job, but is kind of backwards.

The Invoice ID should also have the Customer ID. Yes, it's somewhat redundant, as it's already in the Job table, but it would just make things easier down the line, for reports, etc..

Since both of the above would be scripted, the question becomes how to identify or choose the jobs which go on the invoice. If, as you say, it is based on the month, then it's fairly simple.

I would second Phillip's suggestion to post the file, perhaps only a clone would be sufficient. But you might want to try the above first. Because if you've put the Job ID into the Invoice, that's the problem.

Edited August 11, 2007 by Guest