How have you set up your tables and relationships?
Typical invoicing systems have these tables:
LineItems is were you list each item to be billed on a given invoice. Products_Services(optional) is your "catalog" of available items and/or services for which you might bill a client. Your Invoice number is an auto-entered serial number field in the Invoices table while your services for which you are billing people would be listed in LineItiems.
Here's a simple demo file that uses this setup: http://fmforums.com/forum/showpost.php?post/309136/
Thank You for your response.
I do have the kind of set up you mentionned; however my situation is a little different.
We are a non profit organization offering medical transportation services to clients. Each client request many services throughout the month and each service has it's own ID. These services are then billed to a third party at the end of the month.
So what I have been doing, is making a summary of the transports (services) each person received; and on this summary (invoice) I have the information about the services. The ID number , date , etc (line Item details). The third party has requested that this summary (invoice) has and ID number.
So, in essence I would like to generate and ID number on this summary. Since these summaries are not new records; I have not found a way to do this.
I have also run into another problem. When i try to upload a database to a Windows server 2008 , it shows a following message " one or more of your destination folders those not have enough space for the specified Upload or it does not exist" .This has never occured before and I still have a lot of space avaiable. I checked the database folder and tried to change the permission but it keeps going back to read only. I don't know what to do other than restart ther server.
Thanks in advance for the help
Can I restart the server and see if this resolves the problem?
The ID number needs to be on a sort of Ivcoice.
That invoice lists all of the services to one single client.
That invoice can have an Invoice number or ID.
So basically, when you want to note a service you could have a listview that shows:
- Client: John Doe - Month: April
- Client: Jane Doe - Month: March
You click on the clients 'invoice' and add a service.
At the end of the month you make your reports.
Does this sound about right?
Let me know if you need any help setting this up.
If you want I'll make you a litthe example file.
I suggest you add an invoice table as shown in my previous post. Each time a service is provided, you add another row to the invoice. Once you bill an invoice to the "third party", you create a new invoice record and start listing services in its portal.
Thanks for the response,
Sorry but I am still not clear on this.
I understand that I need to create an invoice table, with a portal for the services.
I proceed to choose the client but then how do I get a list of the services this person received in the last month without entering then all one at a time?
You have to enter these services now don't you?
It's my understanding that you are already creating one record each time the service is provided to a single client. The main requirement is that you identify the agency or insurance company to be billed for that service--which can be a field in the client's record so that selecting a client, identifying the details of the service provided (date, type of service, ect.) is sufficient to log the event you then need to appear on the invoice.
You can either make those entries from a layout based on an invoice table via a portal, or as you currently do via a layout based directly on the portal's table. (In my example setup, this would be the lineItems table.)
You can use this relationship for linking invoices to services provided if you enter the data without using a portal:
Invoices::BillingPartyID = LineItems::BillingPartyID AND
Invoices::cMonth = LIneItems::cMonth
LineItems::BIllingPartyID would be a looked up value from the clients table.
cMonth, in both tables would use a calculation that returns the date of the first day of the same month:
DateField - Day ( DateField ) + 1 //select date as the field's return type.
With this relationship, you can log the services first, and then, when you create an invoice for a given "billingPartyID" and month, the appropriate records will automatically link to it. You can use a portal to review this list of billed items, but probably would not use the layout for printing the invoice. (See the demo file I posted a link for earlier that shows a more flexible way to print the invoice.)
I understand your demo file. i use that setup to enter the meal on wheels records.
Now for the invoice part, I am still confused. As per your suggestions I have created all the tables. As per the image I created a service table and a lines table so I now have a lines portal on the services layout so when I add a service record, I do it through the portal.
( I realized that the lines table was required so I do not have a live link for my price field)
Now I have linked all of tables except the invoice table because I do not understand which tables it should be linked to.
Now if you could tell me how to link all of these; and what layout I put the lines portal on to create the invoice.
Also with regards to the billing party; the customer could have more than one. So when I create a service record I choose
1" The customer
2. the volunteer (who does the driving)
3. the billing party ( which could be the customer himself or a social organization or insurance company.
4. the product ( which is a set route ..point A to point B ...with a set price.
Thanks for the help it is greatly appreciated.
In which table do you create a new record each time a service is provided to a customer? Services or LInes?
It looks like you have two tables where you should have one but that could just be a lack of understanding on my part.
I had been creating records in the services table but I realized now that we have a price increase (May 1 ) that
if I make a change to my price field it affects all of the previous service records. so I created the lines tables so my price field is not live.
Since I am creating a new databse for 2012 I can do this but it is in case I have another price increase I do not want the same problem I have now.
so now I create a record through a lines portal on the service table layout; similar to your demo
You have an invoice table In my case it is a service table
You create records through the lines portal and now I do the same.
Hope this helps, Thanks
Sorry my mistake....
I create a record through the services table and through the portal I chose the product
I'm not sure that I understand the link between a "product" and a "service".
Is there only one "product" per service record?
If so, I don't really see the need for a Lines table here. (there are simpler ways to manage price changes.)
You would link invoices to the Services table.
Invoices::BillingPartyID = Services::BillingPartyID AND
Invoices::cMonth = Services::cMonth
With this set up an auto-entered serial number generates your invoice number, but this sets up one invoice for each biling party for each month. You'd create a new record in Invoices, select a billing party, then enter a date so that cMonth can compute the date of the first day of the same month. Then this record matches to all records in services for that month and that billing party.
A service is: a volunteer driving a client from their home to the hospital ( for example)
and a second service would be driving them from the hospital to their home.
a third driving them from their house to the doctor etc..
a product is : ID 1 point A to point B 20 miles price $20.00
ID 2 point C to point D 15 miles price $15.00
ID 3 point F to point G 35 miles price $35.00
coud be two points in the same town or from one town to another.
In a given month client Mr Jones can have been driving 20 times to the doctor or hospital. and Mrs Thomas was driven 12 times to her doctor. Each time the service can be paid by himself, an insurance company or a social organization.
so at the end of each month I do a search by company. and by client
I see company A will be paying for services for Mr Jones and Mrs Thomas I generate a search Company A and Mr Jones from April 1 to April 30
Mr jones received 8 were charged to Company A. I print this summary out on a layout that looks like an invoice ;where it describes each service and all the info recquired , the service id nunber, the date the product description ( point A to point B ) and the price of each. At the bottom I have a total. Company A owes $263.00 for Mr Jones.
I repeat the same procedure for Mrs Thomas They owe me $126.00 for her services.
I thought this was working great but the companies have requested that my summaries ( what I have been using as an invoice) has an invoice number of their own so they can pay me by invoice number instead of indicating each service id number.
So this is where my problem started. since these summaries are not 'created records' than I don't know how to generate a serial number.
Hence my request for help..
Thanks for your patience !
I realize it makes sense to you, but it seems to me that your products are services so I don't see why you have a table for both--let alone a join table linking the two. Do you list multiple "products" for a given service? If you do, the following still works, but I'd generate that invoice report from a layout based on Lines rather than Services if that's the case.
You still need the invoices table, but the requirement that services provided must match by client, billing party and date require an additional pair of fields for the relationship:
Invoices::ClientID = Services::ClientID AND
Invoices::BillingPartyID = Services::BillingPartyID AND
Invoices::Month = Services::cMonth
I've also changed Invoices::cMonth to just Month--a simple date field. Here's why:
First enable "allow creation of records via this relationship" for Invoices in the above relationship.
On the layout were you log a new services record, use the OnRecordCommit trigger to perform this one line script:
If [ Not IsEmpty ( Services::ClientID ) and Not IsEmpty ( Services::BillingPartyID ) and Not IsEmpty ( Services::cMonth) ]
Set Field [ Invoices::Month ; Services::cMonth ]
If an invoices record for this service entry does not already exist, it will create one. If one does exist, this step attempts to modify the record but no visible change will take place as Invoices::Month and Services::cMonth will already have matching values in that case.