10 Replies Latest reply on Feb 21, 2011 6:51 AM by yuichim

    Creating a chart on 2 week basis?



      Creating a chart on 2 week basis?



      I wanted to create a bi-weekly sales chart on having days on X-axis and # of sales on Y axis.
      Basically our payperiod is 2 weeks and I would like the Filemaker to calculate the current date and show the sales amount of current payperiod for our sales people.... Is that possible?

        • 1. Re: Creating a chart on 2 week basis?

          It may be possible. It depends on how you've structured your table from which you want to chart your data.

          Is this table setup so that each day's sales total is in a separate record? Multiple records per day? Each record has the sales date?

          Either way, you should be able to chart it. You can limit the records to a given time frame such as your two week period by performing a find using a date range of Date1...Date2. In your case, Date1 would be the first day of the first week and Date2 would be the last day of the second week.

          The details of how you specify the data for the chart will differ depending on whether or not you have one record of sales data or many for each date.

          • 2. Re: Creating a chart on 2 week basis?

            Hey Phil!  Thanks for your post.

            As far as the table goes, I did not create anything yet so anything is possible.  I was thinking the sames thing.. to create a table for the multiple sales.
            I was going to calculate the number of sales by the total orders made each day.  However the challenge is that I wanted the filemaker to calculate the current pay period automatically and create a chart according to which pay period we are in.
            Now I don't know if this is the best solution but how about creating a table just for the pay period?   It will consists of these fields: payperiodNumber, startdate, enddate.

            What is your thoughts?

            • 3. Re: Creating a chart on 2 week basis?

              The pay period table can work. It doesn't however, look possible to place your chart on a layout based on this table like we would want to unless you have just one record per day in the related table that computes total sales for that day. It's easier to put the chart on a layout based on your orders table.

              1. If you haven't already done so, define a summary field in Orders that computes the total sales
              2. Create a layout based on Orders and and use the chart tool to place a new chart in the body of this layout.
              3. Select the Use Data From: Current Found Set and show data points for groups of records when sorted.
              4. For your x axis, specify Orders::OrderDate
              5. For your y axis, specify the Summary field from step 1.
              6. Now, if you find the Orders records from PayPeriod::StartDate...PayPeriod::EndDate and sort them by Date, you'll get your chart.

              Note: It's possible to set up either a portal to your PayPeriod records or a drop down list of pay periods on your orders layout and then use a script to find and sort the specified orders records when the user selects a pay period from it.

              • 4. Re: Creating a chart on 2 week basis?

                Phil, thanks for your answer as always.

                From what I understand, we have to perform a "find" in the layer to display the chart.  The challenge is that I am doing a "user" find to show a record/lead associated to the account user when they login to filemaker.
                The software I am trying to develeop is for the user to, login and see and check their leads, make invoices by creating orders and keep call records.
                Basically it is a CRM software...
                So when they first login, they will be redirected to a dashboard which I wanted to show couple of charts to display for them.
                ex: sales per day during this pay period (Monday-5, Tuesday-8, Wednesday-10......),  Total leads called during this pay period (Monday-20, Tuesday-43......etc)

                The dashboard layout is a (User table) layout in which I am doing a "find all records owned by user" and so that the user can see all their records in the portal.
                The main reason why I am doing is so that other users can not see each other's records...

                Is this possible?

                • 5. Re: Creating a chart on 2 week basis?

                  You can set up record by record access permissions that keeps users from seeing any records you don't want them to see.

                  See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a detailed description of how to set this up.

                  With this approach, you should be able to do the charting from the layout I specified without having any chance of the user seeing records you don't want them to see.

                  It's also possible for a script to switch from the dashboard layout, perform the find and sort needed for the chart, copy the chart and then paste it into a container field on the dashboard layout so that all the user sees is the chart on the dashboard layout.

                  • 6. Re: Creating a chart on 2 week basis?

                    Hi Phil,

                    I created the payperiod table and did create records starting from 1/4/2006 until 2013.  The fields are: ppnum, dateStart, dateEnd and calculation field (payPeriods showing ex; 8/21/2013 - 9/3/2013).

                    Now since this table is not currently related, when I add a dateStart field in the layout, it is saying that it is unrelated.. Which table should I relate to in order to show the records correctly?  Also I did do the charting as you mentioned, but I wanted to show the chart as:  day 1, day2, day3..... and show 0 sales if there are none, but show the sales on the days there are.   Is that possible?

                    • 7. Re: Creating a chart on 2 week basis?

                      Matching to your sales table?

                      Payperiod::DateStart < SalesTableByDate::SalesDate AND
                      Payperiod::DateEnd > SalesTableByDate::SalesDate AND
                      PayPeriod::gSalesPersonID = SalesTableByDate::SalesPersonID (gSalesPersonID is a global field)

                      From a Payperiod layout you can use the related records option to chart the data after you first assign the correct ID to gSalesPersonID. I'd try this option first to make sure that things are working correctly. Then, since you want "Day 1, day 2 ..." instead of the actual dates (I think I'd use Day ( Date ) myself, but it's your project here), you can switch to the delimitted data option and use List ( "day 1" ; "day 2" ; ....) for the independent axis of your chart but I'm having trouble coming up with an expression that will return zero if there are no related records for a specific day in the pay period.

                      I can do it with 10 different relationships to the sales table, one for each day of the pay period as that makes this expression possible:

                      List ( Sum(Day1::Sales) + 0 ; Sum (Day2::Sales) + 0 ; .....Sum ( Day10::Sales) + 0 )

                      But that seems really cumbersome here!

                      • 8. Re: Creating a chart on 2 week basis?

                        Ok, I'm sorry Phil.  I am getting a little confused as I am going deeper.  Let me go back and start from the beginning.

                        When a user logs in, the startup scripts does the following:

                        After going to the Dashboard Layout (Users table), that's where I wanted to place the charts associated with user logged in. (daily sales, etc)
                        There are couple of tables that are related and total of 4 tables (well there are more but in this instance, I think we are using 4)

                        User Table - Account Table (leads) - Orders Table        --> are related
                        PayPeriod Table    --> it has 200 pay period ranges bi-weekly.  ex: 2/9/2011 - 2/22/2011, 2/23/2011 - 3/8/2011

                        So now, where should I relate the payperiod table to?  I think this is where I am getting lost...

                        I am sorry if I am being stupid....Embarassed

                        • 9. Re: Creating a chart on 2 week basis?

                          Charts are still very primitive in FileMaker.

                          I don't think you can define your chart on the dashboard with this approach and still use the payperiod table in a relationship for it as you can't set up the needed relationship to get to the related sales records after first linking to the pay period table and still be able to chart the data.

                          You'd need a pair of date fields in UserTable to link directly to your sales data. You could use your pay period table as a source of values for a value list to make it easier to select a pay period in the date fields of your user table, but it can't be part of the relationship used to chart the data unless you put the chart on a pay period layout.

                          • 10. Re: Creating a chart on 2 week basis?

                            Thanks Phil,

                            So I think as a solution for now, I will create a layout trigger script that goes that gets the correct chart in PayPeriod layout and copy the image, and paste into the User layout.  That means that I have to create cople of "chart" container fields to store different charts (ex: daily sales, # of calls, etc).

                            I am going to let this sink in and think about.  Thank you for your help!!