7 Replies Latest reply on Oct 5, 2011 2:26 PM by philmodjunk

    Creating a new record in a new table from summary fields?

    kathleen_r

      Title

      Creating a new record in a new table from summary fields?

      Post

      My main table contains daily sales numbers for multiple locations. One record per day, per location.

      I've got the weekly summary worked out, and it works beautifully, however I can see value in having a table that holds those values, as I need to do more things with them.

      Is it possible to create a new record in a new table to hold these?

      If so, how would I go about it? I think I might be using the wrong search terms.

      Thanks.

        • 1. Re: Creating a new record in a new table from summary fields?
          philmodjunk

          I can see value in having a table that holds those values, as I need to do more things with them.

          What "more things" do you need to do? What you propose denormalizes your data (Stores same info in more than one place) and this complicates your solution. It may indeed be necessary, but before you go that route make sure that you really need to.

          We summarize data into a summary table on a nightly basis. This generates one record per line item type per day with a daily total for each. The method we use could be adapted to what you want to do here. (In our case, we need to pull up summary and cross tab type reports spanning up to 5 years worth of invoices so we needed the improved performance we get by working with summary reports drawn from a table with a much smaller number of total records than we'd get form the original line items table.

          In basic out line form, we use a looping script that does the following inside the loop:

          "Loop"

          Perform a find for all unmarked records
          Exit loop if no records were found
          Perform a find for all unmarked records of the same lineitem type as the current record
          Create a new record in the summary table with this ProductID and add totals from a summary field in the line item table that compute total and average prices. (This can be done with pairs of set variable, set field steps  or you can also set up a relationship with "allow creation" enabled that can be used with set field to generate the new records in the summary table.
          Use replace field contents to "mark" the summarize records

          "End Loop"

          This is script is run from a server schedule to take place after business hours so as not to interfere with users.

          • 2. Re: Creating a new record in a new table from summary fields?
            kathleen_r

            Weekly sales figures are used to calculate royalty and advertising amounts, which corporate then takes out of our bank account. The royalty and advertising amounts are marked off on our current spreadsheet as they clear the bank.

            One transaction, per location, per week.

            I also need to set the status of those transactions as reconciled.

            It didn't make sense to me to add a field on the daily sales for that, as it's not a daily occurence, and that's 126 statuses for 18 transactions.

            But I could be thinking about the whole thing wrong. I haven't tried it yet, it's still in the thought process.

             

            Thank you, as always.

             

            • 3. Re: Creating a new record in a new table from summary fields?
              philmodjunk

              You could set up a table of related records where one record equals each weekly total, but linked to the original sales table so that these records are used to record the status, but refer via relationship to the original set of sales totals to compute the totals. That keeps the data normalized and simplifies the process for tracking the bank transactions and reconciling them to the original sales data.

              • 4. Re: Creating a new record in a new table from summary fields?
                kathleen_r

                That sounds exactly like what I was thinking. I just don't know enough to articulate. Forgive me, I'm a newb. How do I set up the new table to do that? (The weekly total part, I think I can figure out the rest)

                 

                 

                 

                • 5. Re: Creating a new record in a new table from summary fields?
                  philmodjunk

                  It depends on what you use for "week" and "year". Is each week a week of the calendar year or a fiscal year? The first business day in January isn't always a Monday so a 'week' can split between December and January. Thus, your business practices will determine what you mean by "Same week" and I don't know that yet.

                  The essential relationship between the tables would be:

                  WeeklyTotals::Week = SalesFigures::cWeek

                  Week might be a number, text or date field and cWeek is a calculation field that computes a common value for all SalesFigure records of the same week. A date is most flexible, but can be a challenge to produce in some cases depending on how you need to handle the details that I described in my first paragraph. Another field in WeeklyTotals could be set up with checkboxes so that you can check off which have those matching bank account transactions.

                  With the relationship working, there are two ways to display the weekly totals on a layout based on WeeklyTotals:

                  You can define a calculation field in WeeklyTotals that looks someting like this:

                  Sum ( SalesFigures::SalesAmount )

                  You can define a summary field in SalesFigures that computes the total of SalesAmount.

                  You then place either the calculation field or the summary field on your layout and it will show the weekly total for that week. Since you can also use the summary field in SalesFigure based layouts to show totals and subtotals, I'd use that approach here as you can use the same field in both layouts.

                  • 6. Re: Creating a new record in a new table from summary fields?
                    kathleen_r

                    Week is a number. I set up a calender table with week numbers and fiscal years. Sales::Week is a lookup from the Calendar::Week wiith a match field of Date. Fiscal Year also works the same way, since the week numbers start over every FY.

                    (You've been so helpful in the past, I'm the one who has to match Friday in Week 34 this year to Friday in Week 34 last year, regardless of date; weeks run Sun-Sat, and Week 1 contains January 1, always - FM WeekofYearFiscal be damned)

                    I hate to be so dense, but I got a little lost with your response.

                    I do not need to display weekly totals on any other layout, but I do need to use those numbers in other calculations. Currently, they only exist as summaries, based on the found set, and sorted by week. I need them to exist on their own, and I don't know how to get there.

                    This is not setup correctly yet, and I'm not sure how my relationship to Sales needs to go, but it's a good example of what I want to happen....which is basically, the total of Sales::CalcSales for WeekYear (per location) to dump into one field, Weekly Sales (per location), where WeekYear is, for lack of a better solution, the sum of the week number and the fiscal year. Since I have multiple week 34s, but only one per Fiscal Year.

                    The end result of this data is a report to display only the unreconciled calculation fields (and their corresponding StoreIDs), with a checkbox to mark as reconciled.

                    That's all I need on that layout. But first I need the weekly totals (per location) to calc the numbers.

                    I appreciate your patience and your help. It's hard to try to be clear and concise when I have no idea what I'm talking about.

                     

                    • 7. Re: Creating a new record in a new table from summary fields?
                      philmodjunk

                      I do not need to display weekly totals on any other layout, but I do need to use those numbers in other calculations. Currently, they only exist as summaries, based on the found set, and sorted by week. I need them to exist on their own, and I don't know how to get there.

                      If you can display the totals on a layout based on the new table, you can also use them in calculation fields defined in that new table.

                      If you have the correct relationship defined, WeeklySales can be defined to use the Sum function as I described earlier, or the Royalty, AdFund and association fields can be defined like this: Sales::SummaryField * 0.05   // just subsitute the appropriate factor for the other two calculations.