Discussions

Expand all | Collapse all

Import Actual Fiscal Weeks

  • 1.  Import Actual Fiscal Weeks

    Posted 12 days ago
    I would like to run an automation of sorts, where a user inputs a year into a parent table and then i would like to run an automation that creates 52 children records for fiscal weeks. Outside of just having a number 1 thru 52, is it possible to have the actual fiscal date of that year populate into a field so that i can do reporting off of that?

    As the fiscal week changes each year, not sure if quickbase's native formulas could cover this.

    ------------------------------
    Rob Salaj
    ------------------------------


  • 2.  RE: Import Actual Fiscal Weeks

    Posted 12 days ago
    Edited by Mark Shnier (YQC) 12 days ago
    Yes, this is possible. Can you explain what the rule is for determining what the fiscal week is for each of the 52 weeks? Is this like the Sunday of when the week begins? If so can you explain what the rules are for that and also do you have to deal with years that have 53 weeks.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Import Actual Fiscal Weeks

    Posted 12 days ago
    Edited by Evan Martinez 12 days ago

    I think this is a good website for the formula - https://www.epochconverter.com/weeks/2020

     

    All weeks for US start on Sunday and end on Saturday.

     

    Yes we should deal with the 53 weeks in a year. If we can create a formula with a from date and to date maybe that will work well.  

     






  • 4.  RE: Import Actual Fiscal Weeks

    Posted 11 days ago
    My approach would be like this.

    Set up a table with a single record and a field called Automation Focus with numeric fields called [Year] and [Record ID# of Parent Record]

    Set up a table called 53 Weeks with exactly 53 records.

    Make a relationship where 1 Year record has many weeks based on a formula reference field with a formula of 1.  Lookup the [Year]  and the [Record ID# of Parent Record] down to the 53 weeks table.

    Make a formula date field in weeks with the following formula

    var number YYYY = [Year lookup];

    var date WeekRaw = FirstDayOfWeek(FirstDayOfYear(Date($YYYY,1,1))) + Weeks([Record ID#]);
    If(Year($WeekRaw) =$YYYY, $WeekRaw)

    Then make a saved table to table import from 53 Weeks into the child table, mapping the [Record ID# of Parent Record] into the field [Related Parent].  On that import filter where the Week is not blank.

    On the parent record presumably there is some kind of Date field.  One way or the other, maybe by direct data entry, get the Year onto that record.

    Create an Automation triggered by say a checkbox being edited on a Parent record.

    Step 1. Modify the Automation focus record to copy in the Year and the Record ID#]
    Step 2 Run the import.

    That should do it!  Post back if you get stuck or contact me for direct one on one assistance.




    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Import Actual Fiscal Weeks

    Posted 5 days ago

    Hi mark,

     

    This helps. If I may ask one more question. I currently have a TRIR Year table and a TRIR Weeks table. Given your solution, and your first sentence with regards to setting up a new table with a single record for automation focus? – should that be a new table or should I use the TRIR year table?

     

    My workflow for the user would be:

    • At the start of the year the safety manager will create a TRIR year record. This would trigger the automation to create the fiscal weeks.

     

    Before I begin, I just wanted to clarify this. Do you see using 2 tables or 3 total.

    Thanks

    Bob

    Bob Salaj

    Director of Operational Excellence (OpEx)

     

    Direct

    724-820-1534

    Mobile

    412-335-8168

    Web

    LighthouseElectric.com

    http://lighthouseelectric.com/sites/lighthouseelectric.com/files/lighthouse_electric_email_logo.jpg

    http://lighthouseelectric.com/sites/lighthouseelectric.com/files/lighthouse_electric_email_facebook.jpghttp://lighthouseelectric.com/sites/lighthouseelectric.com/files/lighthouse_electric_email_linkedin.jpg

     






  • 6.  RE: Import Actual Fiscal Weeks

    Posted 5 days ago
    Right, I was not sure where these children were to be attached to.  But now I know that One TRIR Year has Many TRIR weeks.  So you can enter a TRIR Year and have that trigger the Automation, so no need for a Automation Focus table, so just the two tables which you already have.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------