Tell us a little about your workflow and your structure.
What do you do and in what order? I imagine you are creating procedures and setting the timestamp on creation of the procedure.
And at some point you create an invoice. How do you do this? And what happens record wise? Do you relate these procedures to an invoice? Do you import them into a new table? Is your procedures table actually like a line items table? Or line an invoice detail where one or several procedures are related to a particular invoice?
Maybe a screenshot of the relationship dialogue might help.
If you have an invoice record and related lineitems records that contain timestamps you can just calculate the difference per line item. Over the relationship bewteen the Invoices and the lineitems.
But I know nothing of your structure, so I can't really make any usefull suggestions.
I have a customer layout that includes a tab labeled Procedures. I have a Procedures table.
I then have a layout for Staff. The Staff table is related to another table called Invoices. I wanted the invoices linked to the Staff not the customer because customers do not always get an invoice of this type dependent on the type of Procedure. I also wanted to build a summary calculation for certain invoices that was based on the Staff because they get paid on commision from these invoices. Here is how I had it set up.
CustomerTO---->ProcedureTO (Related via auto id to idfk)
StaffTO------>InvoicesTO (Related via auto id to idfk)
ProceduresTO------->InvoicesTO2 (Related via auto id to Invidfk)
I could build the invoices just fine, but I could not pull information from a particular procedure to a particular invoice. Both the Customer Procedures and Staff Invoices are built in a portal on the respective layouts.
Hope this helps.
A procedure is always built first and is attached to the customer. The invoice is built later after the procedure is done indpendently of the procedure. Procedures are related to customers. Invoices are related to staff. So the invoices and procedures are independent of each other. I do not want to build the invoice at the time that the procedure is entered because I want the time stamps to be different. Also, while I have several different procedures I only have one procedure which requires a seperate invoice to be built. I want those invoices to be keyed off the particular staff member not the procedure.
Here is a snapshot of the Particular Layout and Tab
This layout you are showing, what is it based on?
Is it based on "Customers"?
I also see some strange things in your relationships.
Like there is an InvoiceFk field in your invoices. But it doesn't appear to be doing anything.
And there is a TatooInvoices 3. But that's a pretty strange relationship.
The way I see it is that every Procedure is related to a customer. And this procedure contains a timestamp.
Then every invoice also has a "customer" and "procedure number" field. So every invoice can be related to a customer and a procecure.
When you do that you can get the related timestamp from the procedure and calculate the difference with the timestamp in the invoice table.
I assume that you could place a "Create invoice" button on your procedure layout that immediatly takes the Procedure number and customer ID with it and enters in in the new invoice. It would also create the "On Creation timestamp" in the invoice record so your calculation in the Invoice table can immediatly calculate the difference.
You just haven't properly related the Invoices and procedures TO's.
They need to be related trough the ProcedureIdFk field in the Invoices table.
Thank you, for your time. I am new to relational databases and I am sure the tables are sloppy just from me trying all sorts of different combinations. Try as I might (and I tried everything I knew to do), I just could not get the Invoice table to properly relate to the the Procedures table and the Staff table and have the portals fill properly in the separate layouts. I could do one or the other, but not both. So I devised a work around. What I really needed was the time stamp from the procedure so I devised a simple script for a button when you want to create the invoice that copies the stamp from that procedure and sends you to the portal layout to input the invoice.
It has the added benefit of placing the portal row on top where you are entering the invoice because the Set Field to "No" causes a refresh and the sort for the portal is by "No" and most recent date.
Maybe in the future I will figure out how to relate everything so I don't have to "work around" this stuff. Until then...Thank you.