5 Replies Latest reply on Nov 17, 2011 9:41 AM by philmodjunk

    guidance needed on best layout /relationship approach for setting up invoicing of jobs

    grzlyhippo

      Title

      guidance needed on best layout /relationship approach for setting up invoicing of jobs

      Post

      Hi i have a database which works with projects and billing of time, services and printwork. Presently I search the billing layout by date range (per month) making necessary changes before this needs to be invoiced e.g. (mark up, Vat included, admin costs). On the  Billing Layout is a category of Time sheets, bought in Services, Printwork-to organise the costings. Details of Time billing, printwork and services are stored in a seperate table (as line items) by inputing details via a portal on the job sheets. 

      Jobs are not necessarily completed before invoiced, clients are billed for the work done for that month. Clients are billed for many jobs to one client, and they receive one invoice.

      I have looked at various solutions which suggest using a portal but many of the ones visited use a look up method of influencing what results on the invoice. Unfortunately this is unrealistic as an invoice can have between 50-100 seperate jobs, some of which may or may not include services, printwork.

      This is my main dilemma, which layout would be best to access the information which has been  "worked" on a different layout.

      I also need to find a way of searching the information on billing layout and combining the time and services copying the details into an invoice layout and then including printwork for that month in the same invoice but seperated from the other two in the layout even if they involved in the same job no. 

      I'm going round and round in circles and would appreciate some thoughts on the best approach/ or options available to me.

        • 1. Re: guidance needed on best layout /relationship approach for setting up invoicing of jobs
          philmodjunk

          I have looked at various solutions which suggest using a portal but many of the ones visited use a look up method of influencing what results on the invoice. Unfortunately this is unrealistic as an invoice can have between 50-100 seperate jobs, some of which may or may not include services, printwork.

          I don't see a problem with look ups. Look ups are based on relationships you define so you should be able to set up look ups that look up only the data appropriate for that record in the portal row.

          I don't see a need to "copy the details into an invoice layout". Since you have this data entered already into tables, the task would be to link the correct records from these other tables to your new invoice record so that you can send them a bill.

          Can't go into many details here as I don't know how you have currently set this up.

          What relationships have you defined linking your tables to each other? (A screen shot of Manage | database | relationships could be very helpful here.)

          • 2. Re: guidance needed on best layout /relationship approach for setting up invoicing of jobs
            grzlyhippo

             Thanks for answering.

            Here are the relationships of the database. TO's on left of screen are work in progress and have no influence on the database at the moment.  primarily the database consisted of TO's -Project Manager (pink), Time Billing (blue) and UPdates (green).

            I was playing around with this today trying out the concept of adding TO's of printwork and time/services to store details seperately in order to influence the invoicing and this is as far a I got today as i'm clutching at straws again. 

            Also I wasn't clear enough in my description of Portal look ups. Possible solutions have usualy been based on a portal with for example a field which has a value list of job no's, you choose one and then the rest of the details are looked up. It's the value list which would pose a problem as we can have 100's of jobno's active at the same time and it would be too time consuming.

             

            • 3. Re: guidance needed on best layout /relationship approach for setting up invoicing of jobs
              philmodjunk

              It's the value list which would pose a problem as we can have 100's of jobno's active at the same time and it would be too time consuming.

              When dealing with large value lists, there are two methods you can use to make them more manageable (and the methods are not mutually exclusive):

              You can set up a conditional value list so that you select a category in field 1 and this limits the values listed in field 2 to only those that are a member of the specified category.

              You use a scripted method where you enter all or partial text to get a list of values matching that text from which to select the specific value. Here's a demo file that illustrates several such enhanced value selection techniques:  http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

              • 4. Re: guidance needed on best layout /relationship approach for setting up invoicing of jobs
                grzlyhippo

                Thanks for these ideas. I especially like the add a selected item portal influencing the pricing portal, and I think this could work for me. I have some thoughts on using the portal to do a date range find and then sorting Jobno before transferring details across. Finally is there anything I need to be aware of when using the information inputted into a portal for a report/ invoice layout?

                Out of office for few days but will attempt to work these on Tuesday and will contact again if any problems. Again Thanks for your time.

                • 5. Re: guidance needed on best layout /relationship approach for setting up invoicing of jobs
                  philmodjunk

                  Take a look at how the Invoices starter solution prints an invoice. The data is entered into a portal, but the invoice is not printed from a layout that uses that portal. Instead, the line item records are pulled up on a list view layout based on the line items portal for printing. This is a much more flexible way to handle printing issues that can occur when you have more items in your portal that you have rows in the portal.