1 2 3 Previous Next 102 Replies Latest reply on Mar 25, 2010 3:42 PM by philmodjunk

    Creating a Budget Sheet From Certain Records

    Link2430

      Title

      Creating a Budget Sheet From Certain Records

      Post

      I am using FileMaker Pro 10 Advanced on OS X and am creating a database for an advertising agency to make workflow more efficient. I started using FileMaker last week, but am a Computer Science major and have basic database knowledge.

       

      I have two layouts as of now. One is the starting point which holds the three different types of purchase orders using tabs (Print, Broadcast, and Miscellaneous). The other is a Budget Sheet which needs to be updated as new purchase orders are created. The starting point layout has tons of fields, but the ones that are relevant to the budget sheet are start and end dates, media name, client, and cost. What I need FileMaker to do is to create budget sheets for each client for every month whenever a budget sheet is requested. I was wondering if I could make the client name the primary key between the two tables. I'm not 100% sure if that will work or not. I can't mess with the design too much since these items are printed and faxed to the clients.

       

      I have separate columns in the budget sheet for print, broadcast, and misc media types. For each purchase order there is one media name, one date, and one cost. I need FileMaker to create a budget sheet for each client for each month with every relevant purchase order's information (media name, dates, cost), but also change the correct budget sheet when a purchase order is added for that month for that client. Do these questions make sense?

       

      I've started and stopped with a ton of scripts, tried portals, etc etc. I'm either not understanding databases fully or am missing something that seems fairly simple. Any ideas would be greatly appreciated. Thanks! 

       

       

        • 1. Re: Creating a Budget Sheet From Certain Records
          philmodjunk
            

          First a key detail you've asked about:

           

          "I was wondering if I could make the client name the primary key between the two tables. "

          That's not a good idea. In filemaker you should set up your primary key as an auto-entered serial number. Name fields (whether individuals or companies) are subject to change and also can be mistyped. If you link your data by a name field, such issues will create massive headaches for you.

           

          Overall, it's difficult to comment in detail about your database design as it is the details that will make or break your project. Several of your descriptions suggest you might be attempting to treat your database as a spreadsheet instead of a database: "The starting point layout has tons of fields... ", "I have separate columns in the budget sheet for print, broadcast, and misc media types...." I could be mis-reading you, but if I'm right, you need to take a step back and view each row of data as a separate record and use list view, table view and/or portals to display these multiple rows of data. The resulting structure of your database could be much simpler and thus much easier to work with.

           

          You might want to click the advanced search link and search for threads that deal with invoices to get some examples of how others have done this.

          • 2. Re: Creating a Budget Sheet From Certain Records
            Link2430
              

            You're right about the spreadsheet thing since these forms were originally part of a spreadsheet program. I was asked to help automate the tedious parts of the process and came upon FileMaker as a decent solution. 

             

            I'm not sure I understand what you mean about viewing each row of data as a separate record. Each purchase order has several columns for information to be inserted into. Are you saying I should view each row of entry in those columns as separate records? Sorry, I know I'm coming across unexperienced.

             

            Thanks for your quick reply. 

            • 3. Re: Creating a Budget Sheet From Certain Records
              philmodjunk
                

              That's exactly it. To translate a typical spreadsheet into a database table that can store the same data, treat each row as a different record and each column as a field you need to define for that record.

               

              Try the following experiment.

               

              Define three fields in a new table:

              Name

              Address

              Phone.

               

              Leave Manage Database and Select Table View from the view menu.

               

              Create and enter several names and addresses, using New Record to create new rows so you can type in additional data.

               

              See how that looks quite a bit like a spreadsheet?

              • 4. Re: Creating a Budget Sheet From Certain Records
                Link2430
                   Yes that makes perfect sense. To use your example further, let's say Name, Address, and Phone are the same for several different types of orders. So on the same record with Name, Address, and Phone would I also have fields to fill in the various specifications for that purchase order? I think the design of this is starting to unfold in my head, I've just still got a few hitches to work out.
                • 5. Re: Creating a Budget Sheet From Certain Records
                  philmodjunk
                    

                  Remember that I mentioned searching the forum for Inovice threads?

                   

                  A typical invoice in Filemaker consists of an invoice table where one record = 1 sales transaction to one customer and a second related table of line item records where one record = 1 type of item purchased on that invoice.

                   

                  Thus, in an invoice like:

                   

                  John Smith 2/16/2010

                   

                  2 widgets $0.20 $0.40

                  3 Things   $5.00 $15.00

                   

                  The information shown in black is stored in an invoice record and the information shown in blue comprises two related records in the line itmes table. Typically, the line items are displayed in a portal which makes data entry easier. You can look up portals in filemaker help to learn more about them.

                  • 6. Re: Creating a Budget Sheet From Certain Records
                    Link2430
                      

                    I'm starting to make headway thanks to your help. I was thinking too much in terms of spreadsheets to realize that the tutorial I went through would work for this project. 

                     

                    Anyway, right now I have a report with two sub summary areas that sort the layout by Client and Month. The problem is that it creates a new record for each Invoice. I want it to keep each invoice that contains the same client and month on one record. In other words, a summary sheet with several fields from each invoice for each client for each month. I tried using a portal, but to no avail. 

                     

                    I would like it to look like this:

                     

                     

                    Client

                    Month, Year Current Date Company Logo

                    _____________________________________________________________________ 

                     

                    Media Dates Budgeted Amount

                    news 2-28 $8,000.00

                    news2 3-9 $4,000.00 

                     

                     

                    Right now it would put news and news2 in separate records. Any ideas? 

                     

                    • 7. Re: Creating a Budget Sheet From Certain Records
                      philmodjunk
                        

                      This certainly can be done, but I'm not sure I can interpret your table structure correctly from your last example.

                       

                      Does

                       

                      news 2-28 $8,000.00

                      news2 3-9 $4,000.00 

                       

                      Represent two invoices or two items on the same invoice?

                       

                      In either case, I put together a tutorial for making a summary report based on a simplified invoice. Take a look at it and see if it helps you figure out how to do what you need:

                       

                      Creating Filemaker Pro summary reports--Tutorial

                      • 8. Re: Creating a Budget Sheet From Certain Records
                        Link2430
                          

                        I want those items to represent separate invoices, which would have different ID numbers. 

                         

                        Would portals still be the way to go there? I couldn't get them to cooperate. 

                        • 9. Re: Creating a Budget Sheet From Certain Records
                          philmodjunk
                            

                          I wouldn't use portals for this. It can be made to work, but is less flexible.

                           

                          Did you check out the link I posted? A variation of that summary report will produce what you want. Just base your layout on invoices instead of line items.

                          • 10. Re: Creating a Budget Sheet From Certain Records
                            Link2430
                              

                            Yeah I looked at that link. I based the layout on the Invoice ID instead of line items but it still shows one record per line item. I'm trying to get each record to hold every invoice from the entire month for one client.

                             

                            I need to be able to print that one record as a budget sheet for that month. The next record would be a different client, but maybe the same month. Does that make sense? 

                            • 11. Re: Creating a Budget Sheet From Certain Records
                              philmodjunk
                                

                              Go back and check the variations at the end at the end of the tutorial .

                               

                              You can set up a sub summary part that groups your records by month. You can place invoice summary fields in this layout part and then delete the body part entirely. That will merge all your invoices of the same month for a given customer into a single line.

                               

                              A simple calculation field you can use to group your records by month:

                               

                              cMonth : InvoiceDate - Day (invoiceDate) + 1

                               

                              Set this calculation to return a date.

                               

                              The date returned will be the date for the first day of the month for that invoice so you can easily sort it by this month and even format it to display only the month and year if you want to put it in your report.

                              • 12. Re: Creating a Budget Sheet From Certain Records
                                Link2430
                                  

                                Thank you very much for your patience with me, but I must not be communicating what I want very well.

                                 

                                I've gotten the records to sort by client and month, but instead of three records that have the same client and month showing different invoices I want one record that shows the client and month with several invoices. Right now I have 12 records sorted by month and client because I have 12 invoices, but I need 4 records with three invoices of the same client and month showing.

                                 

                                Everything I try to do I seem to still end up with 12 records because of 12 invoices when I really just want 4 with the same information as those 12. 

                                • 13. Re: Creating a Budget Sheet From Certain Records
                                  philmodjunk
                                    

                                  What parts show in layout mode on your report layout? It sounds like the last line of your report is the Body instead of a sub-summary part.

                                   

                                  Create a sub-summary part "when sorted by" cMonth.

                                  Place cMonth and a summary field that totals up your budgeted amount in this part.

                                  Delete the body part.

                                  Enter browse mode and sort in a sort order that includes cMonth (Sort by Client, then cMonth)

                                   

                                  That should do what you need.

                                  • 14. Re: Creating a Budget Sheet From Certain Records
                                    Link2430
                                      

                                    The cMonth calculation returned 2010 as the year instead of month. Not sure what I did wrong there. 

                                     

                                    Here is a screenshot of the sub summary report in layout mode which yields twelve different records.

                                     

                                     

                                     

                                    ::Month is a typed in month name which I was using for testing purposes. If cMonth can be used to calculate the month that would be nice to convert that number to text. 

                                    1 2 3 Previous Next