Forum Discussion

AnnetteGioia's avatar
AnnetteGioia
Qrew Trainee
6 years ago

Adding items to a current table to track activity, although item won't be processed in current month

Let's see if I can explain this. I have a Master Records table, some records are due monthly, some quarterly, some annually, etc. We copy the table each month pulling only the records that are due for that month. However, we'd like a way to track the monthly activity for ALL records although all of them won't be "processed" that month. Then once it is due, the idea is to pull the activity by month and process those amounts on that non-monthly record. Say for example, I need to enter sales amounts by states, in the current table, Ohio isn't showing up because it is Semi Annual, but I want to enter the sales on a non-active record. We have filing status and date, etc... as fields in the current table. Would it be possible to add "all" records and grey out the fields that we won't be using that month? And just allow edits on certain fields? Hopefully that makes sense. 
App builders
Ask about Quickbase functionality

5 Replies

Sort By
  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    6 years ago
    Read it a couple of times - and I think I get what you're saying. So basically do a full copy - but flag them in such a way that you can distinguish the ones that should be processed versus the ones that are simply placeholders that you want to do data entry but shouldn't be reconciled/processed etc. Is that right? 

    My suggestion would be to add a formula-checkbox or equivalent in your master records table that would determine based on todays date if that record is going to be processed.

    So if record is monthly, this value is true. If record is quarterly, and today is in February, then false. But if on that same record, since we're now it's March, it toggles to true. If your record is Ohio and semi-annual - this checkbox is only true in June and December

    Since you're copying these records month over month, you can include that checkbox or equivalent as part of your copy and store it in your new records, and then in your 'processing' table - you can just set up reports / filters against that value that was copied. 

    Does that sound like it might accomplish the task? Or did I miss it by a mile?

    Chayce Duncan | Technical Lead
    (720) 739-1406 | chayceduncan@quandarycg.com
    Quandary Knowledge Base
    • AnnetteGioia's avatar
      AnnetteGioia
      Qrew Trainee
      6 years ago
      Thanks so much for your reply. That sounds like it may work. Now my issue would be creating the formula-checkbox in the master records table, as you suggested. The person that handled this before me had set up formulas to match certain filing frequencies. For example, one is named Feb, this will have the records with filing frequencies due in Feb, monthly, occasional. Another one is named Mar, this would be records with frequencies of monthly, occasional, quarterly... etc. I can't find the original source of these "fields/formulas" that he created. I guess I could just create the formula utilizing those fields/formulas that he set up. I wish this were my only job so I could really dive in, but I just manage the applications in addition to running my department. 
    • ChayceDuncan2's avatar
      ChayceDuncan2
      Qrew Cadet
      6 years ago
      To make sure I'm following - you have individual fields set up for each month? And your copy just looks at a different field each month - so this month it's looking at a [March] checkbox? 

      It's a bit of an odd approach - but if the above is right - then your new formula is pretty simple. You can just do something like: 

      var number currentMonth = Month(Today());
      If(
      [January] and $currentMonth=1,true,
      [February] and $currentMonth=2,true,
      [March] and $currentMonth=3,true,
      .....etc
      )

      That lets you check the pre-existing fields - and check the current month to only process relevant ones. I may have also misunderstood your description of how the prior developer set it up. 

      Out of curiosity - what process are you currently using to copy your records each month? 

      Chayce Duncan | Technical Lead
      (720) 739-1406 | chayceduncan@quandarycg.com
      Quandary Knowledge Base
  • AnnetteGioia's avatar
    AnnetteGioia
    Qrew Trainee
    6 years ago
    Sorry, it's difficult to explain in writing, but you are following along great! Ok, so we have the master table, it has 2,553 records. The filing frequencies are Monthly, BiMonthly, Quarterly, SemiAnnual and Annual. The previous developer set it up so that the Jan, Feb, Apr, May, Jul, Aug, Oct, Nov periods include all of the records with a "monthly" filing frequency. The Mar, Jun, Sep, Dec periods include all of the records with a Monthly and Quarterly filing frequency. June would also include the semi annuals and December would also include the annuals. So it isn't necessarily a "field", per say. I have attached images of the formula fields I had mentioned, in case that helps. He set up the filing frequencies using numbers, as you'll see for December (2,553 records), it includes all of the periods. March would have the Monthly & Quarterly filings due (2,118 records), July would have just the Monthly filings due (1,541 records), etc. To copy the records each month, we have reports set up to pull the Mar records for example, then I would choose to copy these records to another table.  

  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    6 years ago
    Based on what I'm reading I think I might have a solution. So based on the above - you already know if something should process each month based on the Jan - Dec field. So - you can use those, and the current month to run you 'Process' checkbox, using a the below formula 

    var number currentMonth = Month(Today());

    If(
    $currentMonth=1,[Jan],
    $currentMonth=2,[Feb],
    $currentMonth=3,[Mar],
    .....etc
    )

    Its a little different than I initially proposed - but in a nutshell it will say, 

    If the current month is January - the whether or not it will process will be the output of [Jan]. So its just copying the yes/no output of that checkbox. If its February, copy the output of that field all the way through december. That way each month its just checking on the right field, and you can simply re-purpose that pre-existing logic without have to do anything overly complicated. Hopefully that sounds feasible. 

    Chayce Duncan | Technical Lead
    (720) 739-1406 | chayceduncan@quandarycg.com
    Quandary Knowledge Base