10 Replies Latest reply on Oct 7, 2010 1:07 PM by philmodjunk

    Summing info from a selection of records and storing the info in another table, creating a new...

    Tessf

      Title

      Summing info from a selection of records and storing the info in another table, creating a new record for each selection.

      Post

      I want to find the sums of certain fields in Table A, where the database will return a separate sum for each of several descriptions listed in Tables A and B (and possibly C as well), where the descriptions will have matching and un-matching instances between A and B (and C). A separate but single record must be created in Table D for each description (of a given class, which classes may be defined differently in Tables A, B and C though something defined as p in table A will always be in the same class as something defined as q in Table B, though there is a class n in table A which has nothing to do with class m in table B), matched or unmatched, to record the given sums.

      A new set of records must be created for every date that this exercise is done. These will be project "snapshots", taken as at a given cut-off date for billing purposes. So there will be a record for each description x as at date y, an new one as at date z etc. in Table D.

      When I enter a date in Table Z, I want this function to rattle off automatically. I currently have to do it manually, by requesting the sums by description from table A, then from Table B etc, then compile them into a small report done in Excel. 

      It is mind-numbing when I am required to do more than a few at a time. And anyway, this is what databases are for, no?

      Is anyone out there willing to help coach me through this? I just can't seem to get my mind around how FmPro10 Adv. needs to have the things defined and the calculations defined or how to create several new records in a second table for one new record requested in another table... I seem to have gotten halfway through and now can't get past the block.

      And going back to do this project by table by project and setting up an excell sheet will have to be done in the short term, but I can't handle that in the long-term. I would rather spend 200 hours on getting FmPro10 Adv. to do it than 10 on getting it done by hand, because by the time I am done doing it the slow way, I have to start again. 

      And anyway, I can be sanctimonious about the future time-wastage to justify my attitude. ',;^)

      In hope (that what I wrote is intelligible and that someone can help...) - 

      Tess

      Report_mock-up.jpg

        • 1. Re: Summing info from a selection of records and storing the info in another table, creating a new...
          philmodjunk

          There's a number of ways to do this with either aggregate functions such as Sum that can sum up related records as well as summary fields which can report either the total of a found set or subtotals of grouped records in the found set.

          But you lost me in all the alphabet soup.

          Try to discribe what you want with real data instead of abstract letters and maybe we can figure this one out.

          • 2. Re: Summing info from a selection of records and storing the info in another table, creating a new...
            Tessf

            I am redefining it with real field and table names at the moment. I think I got confused by the alphabet soup too - I am having to rephrase some things here. But I will put it up as soon as I can - should be in 10mins or so unless I get interrupted. Please don't forget I am only using FmPro10Adv, not 11.

            Thanks, it is encouraging to know that someone out there is at least ready to help. Provided I can clarify...

            • 3. Re: Summing info from a selection of records and storing the info in another table, creating a new...
              Tessf

              Ok, here is my restatement - of what I start with and what I would like to create and why. I hope it is clearer.

              I am sorry I took so long - I decided to clarify what might still be fairly incoherent with a schematic. The lines only show the one-to-many relationships - not the relationships as defined in the database - I have too many of those to put here, tho' I might not have all the right ones. The Project table is obviously the basis. The purple boxes are showing the tables I want to create.

              I have tried to include only and all the relevant fields in this diagram for simplicity

              I hope this will make it clear enough for you to help me. Herewith my latest attempt at problem statement:

              For a given project: I want to find the sums of the hours by job-description in table TIMESHEETS (ie hours logged as worked) and LINEITEM (ie hours already billed to the client), and possibly also PAYABLES (where it would be by sub-consultant, not by job description, but it is still charged as a fee, not a disbursement), where the database will return a separate sum for each of several descriptions listed in TIMESHEETS and LINEITEM (and possibly PAYABLES as well), where the descriptions will have matching and un-matching instances between each of those tables, and may or may not match the job descriptions as defined per project (a necessity when your clients seem to have different descriptions for the same job functions).

              So a separate but single record must be created in BILLABLEHRSTRACK for each description (which will be classed as "billable" in TIME, "Professional Fees" in INVOICE, and all of the records for a project in PAYABLES, matched or unmatched, to record the given sums.

              A set of these records in BILLABLEHRSTRACK as grouped by the cut-off date defined in BILLINGTRACKING will be project "snapshots", taken as at the cut-off date, for billing purposes. 

              Phrased another way, sort of: A new set of records must be created in BILLABLEHRSTRACK for every date entered as a cut-off date in a single new record in BILLINGTRACKING. So there will be a record for each description (such as Senior Landscape Architect, CAD Operator or Consultant x) in BILLINGTRACKING for a given cut-off date for a given project - which are listed in table PROJECT.

              So when I enter a cut-off billing date in BILLINGTRACKING, I want it to automatically create a set of related records in BILLINGHRSTRACK.

              From this I can then generate a report with

              1) Both line-item and time-log subtotals in the body part of the layout

              2) the project totals in the sub-summaries

              3) can refer back to the state of the project at that date in the future

              • 4. Re: Summing info from a selection of records and storing the info in another table, creating a new...
                Tessf

                Oh dear, how do I upload my screenshot of my schematic?

                • 5. Re: Summing info from a selection of records and storing the info in another table, creating a new...
                  philmodjunk

                  In your image, you don't have a table labeled "TIMESHEETS" you have a Time Log table and a LineItems table that both appear to be able to log billable hours--which is redundant. I'd make time log and lineitem entries records in the same table. They'd become LineItem records by assigning an invoice number to them and I'd define an Invoice table where a new record would be created each time the client is billed for services rendered.

                  I think what you want can be constructed from a summary report where you'd find the time log records for a given project and sort them by JobDescription in order to group them and compute subtotals for each. This type of report can list each and every time log entry under each JobDescription category or it can only show the totals for each.

                  Here's a tutorial on setting up a summary report that may prove helpful to you:  

                  Creating Filemaker Pro summary reports--Tutorial

                  • 6. Re: Summing info from a selection of records and storing the info in another table, creating a new...
                    Tessf

                    Ok, my bad, I'm sorry, read "Timesheets for "Timelog". "Timesheets" or "Timelog" is a log of hours worked and which are billable, which are not, showing the day-by day activities of each employee. I am only interested in the job description when they work and the subtotals by job-description, not who does the work or when, except by broad billing period. It also shows, for example, if the time is overtime - so to be billed or paid at 1,5 times, but we need the hours actually worked by the individual for other purposes.

                    The LineItem records are a log of the time actually billed (should be BILLED, not billable, sorry) out to the client to date. It does not show who did the work or when they did it, in what size chunks, over how many days, whether it was overtime or not or anything. It might not always agree with the billable hours - for eg if we have underquoted or not yet received authorization to increase the contract amount, or if we are making allowances for a new employee's learning curve.

                    So for the company purposes, we need the two separate, and combining them into a single table is not an option, since several reports are created using the two tables separately as they are actually showing different info.

                    I am sorry to be so unclear - I usually work with the person who set up our original solution so I forget when I should mention things because other people don't know our system so well, but he is currently unavailable. At least until after the timeframe in which I am hoping to settle this. I will try to be better. I also tend to fail to take into account that people can't read my mind. My husband often complains about that ;^)

                    It is also a big mess in my mind - having to state it like this has clarified it some for me, but not completely. Hence my desperation for help...

                    I do have both Invoice table and LineItem tables. I thought, since the info I am after is actually coming from the LINEITEM table it was irrelevant, but apparently not. Again the relationship from PROJECT to INVOICE is one-to-many, and INVOICE to LINEITEM is one-to-many.

                    This database is live and functioning. I am messing about in a copy trying to create a new module for it. My problem is in sufficiently clearly defining relationships to get info from several tables, all grouped in several ways, to a single table, grouped in new ways. And to create new records in a second table from entering something in a first - but making it do all the necessary creation itself, because I don't know how many are needed or what they are called, so I have to get the system to look that up itself.

                    All the info is there, I am just not good at explaining to it (or anyone else it seems ;^) what I want it to do.

                    I will look at the tutorial and see if that helps, and let you know. Thanks for your help so far.

                    • 7. Re: Summing info from a selection of records and storing the info in another table, creating a new...
                      philmodjunk

                      The main thing that strikes me from your posts is that the structure of your tables may be needlessly complex. That complexity, in turn requires you new table(s) to pull together the disparate records from several different tables into a report that I can only partially visualize at this point.

                      I still think that TimeLog and LineItems could be drawn from the same table though you've described complicating factors that keep it from being a simple as I first thought.

                      Can you "mock up" a copy of the type of report you want to see drawn from this new module?

                      • 8. Re: Summing info from a selection of records and storing the info in another table, creating a new...
                        Tessf

                        Ok, this is more or less what I am envisaging.

                        This database is getting pretty complex, but I believe in making it do the work. I am mostly supervised by the original creator, and he is a great fan of simplicity. It might be a bit more complex than it should be but not much. We have about 20 tables and many more reports and layouts. Everybody wants the data in different forms for different purposes, so I try to oblige. And people keep thinking of ways to make the thing even more useful.

                        This was actually a module mooted right from the start, about a year and a half ago and I am only getting to it now. If I didn't have this time-pressure I would leave it to the original creator with great relief, but it is also becoming an interesting challenge for me. 

                        If  push comes to shove, I will just have to leave it for two weeks when our developer is again available and do everything by hand. But I would at least like to try....

                        • 9. Re: Summing info from a selection of records and storing the info in another table, creating a new...
                          Tessf

                          A couple of things that I meant to make notes of on the form - like when it is contract type C, the (contract_remaining = contract_amount - Fees_billed) - the disbursements are a separate issue.

                          Each project has its own job descriptions, so one might have "senior designer", and the next "principle designer", etc, though I haven't changed them here. So thelist has to be defined per project. This is controlled by our clients. Most don't care what the positions are called and we try to standardise those, but big institutional clients, like a city or something, will have its own set of terms, and another big institution will insist on theirs.

                          I had meant to include the parts too: Header is up to and including "Project name and number"

                          Leading sub-summary by Billing end date is up to and including the headers above the job descriptions

                          Body is the job descriptions and data

                          Trailing sub-summary by Billing end date is up to the end of Comments - then we start on the next leading subsummary.

                          Is there a way to force a leading sub-summary to start a new page if the space at the end of the previous page is insufficient to include it completely? So one can have more than one set of records per page, but each set is entirely on a page, not split across two.

                          Thanks again for giving me this time.

                          • 10. Re: Summing info from a selection of records and storing the info in another table, creating a new...
                            philmodjunk

                            Good thing I downloaded and saved your original schematic screen shot ;-) Otherwise, I'd be groping for ways to describe the solution here.

                            This looks like a pretty basic summary report that should be based on your LineItems table.

                            You would seem to need this layout structure:

                            SubSummary when sorted by InvoiceID (print above)
                            Put fields from related table occurrences here for Job number, invoice date, project name etc.
                            Final rows of this section are the Job Description, Hours Billed, rate, etc column headings
                            SubSummary when sorted by Job Description
                            Put in the Job Description field here followed by Summary fields defined in line items to compute the hours billed, and other numbers.
                            SubSummary when sorted by InvoiceID (print below)
                            Put the invoice totals here and the related comments field from the invoices table. The number values in this section will probably need your consultant to help you set up as they require a thorough knowledge of your database relationships to set up. They'll be a combination of summary fields in LineItems and calculations using aggregate functions defined in other related tables.

                            Controlling page breaks like you describe will not be a simple thing, but it can be done with an additional subsummary part and calculations that compute the number of lines used for each report section as long as there are no sliding parts to your layout.