Discussions

 View Only
Expand all | Collapse all

Compare values with values from previous month

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training09-13-2017 16:13

  • 1.  Compare values with values from previous month

    Posted 09-08-2017 09:18
    Is it possible to store values from report, for example from previous month, so that the progress and the differences can be shown in the following month?

    I have a list of ongoing projects that show contract values for the next 6 months, and on a monthly basis it needs to be reported to the management. Now each month this report needs to be send, and for it to hold CURRENT ongoing projects and contract values, and PREVIOUS.

    Any ideas on how to achieve this?

    Kind regards, 

    Nenad


  • 2.  RE: Compare values with values from previous month

    Posted 09-08-2017 21:01
    Here is how I would do it.  If you choose to follows this and get stuck on any steps post back.

    1. Set up a child table to Projects called Project History. Add fields for any values that you want to capture. 
    2. Set up a saved table to table copy to copy all Active Projects into Project history and map the [Record ID#] field into [Related Project] and map the various data fields.
    3. Make report of Active Projects needing their History recorded.  This will be filtered where the maximum date created of the child records is not during the current month.
    4. Make a formula URL button on the Projects record to record the history and refresh the report. (which will now be blank).
    5. Subscribe to a daily report of Projects needing their history copied.
    I will post that formula soon and how to use the history. Gotta do a call now. 


  • 3.  RE: Compare values with values from previous month

    Posted 09-08-2017 21:40
    OK, so next is that if you just need to get some numerical values floated up to the Project, from the most recent History with the highest Record ID#.  That can be done by having a Summary Maximum of the [Record ID#] on the Project, and lookup down to the Project History.  The do another summary maximum of the Project value subject to he condition that the [Record ID#] is the same as the Summary Maximum Record ID#.

    The formula to run that import will be something like

    var text URL = urlroot() & "db/' & [_DBID_PROJECT_HISTORY] & "?act=API_RunImport&ID=10;

    "javascript:" &
    "$.get('" & 
    $URL & 
    "',function(){" &
    "location.reload(true);" &
    "});" 
    & "void(0);


  • 4.  RE: Compare values with values from previous month

    Posted 09-12-2017 09:18

    Hi, thank you very much for this it seems it could be extremely useful :)

    Is it possible to organize a quick webex session to cover some of these parts, since I have couple of questions and would like to avoid asking tons of questions in a wall of text?

    My private email is ilic_nenad@ymail.com.

    Thank you very much in advance.

    Kind regards,


    Nenad Ilic


  • 5.  RE: Compare values with values from previous month

    Posted 09-12-2017 16:21
    Hi Mark, so here is the situation and hopefully you'll be able to help me proceed step by step (or clarify how some of the tasks are done from above).

    I have 3 Tables:
    1. Project Manager (list of users)
    2. Customer profile
    3. Project
    where tables 1 and 2 are connected with the Project table in a following manner:
    Project manager - Project (one to many)
    Customer profile - Project (one to many)

    Now I've created Project history table (where I want to save monthly data) and have created the following fields:
    • Project Name (Project table)
    • Customer name (origin in Customer profile, but is part of Project table via relationship)
    • Cutomer region (origin in Customer profile, but is part of Project table via relationship)
    • Project Manager (origin in Project Manager table, but is part of Project table via relationship)
    • Annual Contact value (Project table)
    • Target date (Project table)
    • Status (Project table)

    Now going back to your steps (lets cover 1st steps 1, 2):
    1. Set up a child table to Projects called Project History. Add fields for any values that you want to capture.
    I've created a table with information from above. Am I supposed to define the field identically to the field they would be getting information from? (example: text - text; multiple choice - multiple choice) or is there some other way


    2.Set up a saved table to table copy to copy all Active Projects into Project history and map the [Record ID#] field into [Related Project] and map the various data fields.
    How can I do this, would it be a problem to explain it a bit?

    3. Make report of Active Projects needing their History recorded.  This will be filtered where the maximum date created of the child records is not during the current month.

    To create this from Projects table? How to achieve the second part maximum date created of the child records is not during the current month?


  • 6.  RE: Compare values with values from previous month

    Posted 09-12-2017 16:33
    You can lookup as many fields as you like, as they are just for your convenience and do  not affect the process.

    Based on your post above, it looks like you need to create 3 data entry fields on your history form which will have the same type (numeric or text) that the source fields have.
    • Annual Contact value (Project table)
    • Target date (Project table)
    • Status (Project table)

    To set up a save table to table copy, go into the History Table Home page more button will allow you to set up a saved table to table copy.

    Let get that far and get some child records created and then go to step 3.


  • 7.  RE: Compare values with values from previous month

    Posted 09-12-2017 16:51
    Is this the option to save table to table copy (it's end of my working day, so my brain is a bit slow) :( Somehow I don't understand this step



  • 8.  RE: Compare values with values from previous month

    Posted 09-12-2017 17:01
    Also, from the entire list of needed fields:
    • Project Name (Project table)
    • Customer name (origin in Customer profile, but is part of Project table via relationship)
    • Cutomer region (origin in Customer profile, but is part of Project table via relationship)
    • Project Manager (origin in Project Manager table, but is part of Project table via relationship)
    • Annual Contact value (Project table)
    • Target date (Project table)
    • Status (Project table)

    you've stated that I would need only:
    • Annual Contact value (Project table)
    • Target date (Project table)
    • Status (Project table)
    Where am I going to drag out the information about 
    • Project Name (Project table)
    • Customer name (origin in Customer profile, but is part of Project table via relationship)
    • Cutomer region (origin in Customer profile, but is part of Project table via relationship)
    • Project Manager (origin in Project Manager table, but is part of Project table via relationship)
    is it going to be achieved via some relationship, or are these 4 fields also to be entered into Project history table? (if yes does field setting need to be same as in initial tables)?


  • 9.  RE: Compare values with values from previous month

    Posted 09-12-2017 17:11
    Sorry, I was probably not clear enough.  

    Go to the table home page for the history table.
    locate the more button and then select import / export

    That will allow you to copy in records from another tale and you follow that process to set up a saved table to table copy.


  • 10.  RE: Compare values with values from previous month

    Posted 09-12-2017 17:12
    As for the lookup fields, you can have as many lookup fields as you like.  They will not affect this process we are building.


  • 11.  RE: Compare values with values from previous month

    Posted 09-12-2017 17:31
    Steps 1 and 2 completed:
    - relationship between tables established, 
    - data was copied 

    Now for the step 3:
    Make report of Active Projects needing their History recorded.  This will be filtered where the maximum date created of the child records is not during the current month.

    So as I see it I need to create a report with filter - all projects that are active. What needs to be done for the part -  where the maximum date created of the child records is not during the current month


  • 12.  RE: Compare values with values from previous month

    Posted 09-12-2017 17:48
    OK, good for you.  Good progress.

    So now we need a summary field on the relationship between projects and history.  So go to that relationship and on the left side there will be a button to create a summary field.  Make a new field called Maximum Date Created of Project History.

    Once you get that, then go back to that report of Active projects and add a filter


    where Maximum Date Created of Project History is not during the current month.


  • 13.  RE: Compare values with values from previous month

    Posted 09-13-2017 07:21
    Done :)

    With step 4: Make a formula URL button on the Projects record to record the history and refresh the report. (which will now be blank).

    So I presume Formula URL button is Formula URL field, or did you have something else in mind? 

    After creating it, I inside the formula you've written has to be copy-pasted

    var text URL = urlroot() & "db/' & [_DBID_PROJECT_HISTORY] & "?act=API_RunImport&ID=10;

    "javascript:" &
    "$.get('" & 
    $URL & 
    "',function(){" &
    "location.reload(true);" &
    "});" 
    & "void(0);"


    For this part:
    OK, so next is that if you just need to get some numerical values floated up to the Project, from the most recent History with the highest Record ID#.  That can be done by having a Summary Maximum of the [Record ID#] on the Project, and lookup down to the Project History.  The do another summary maximum of the Project value subject to he condition that the [Record ID#] is the same as the Summary Maximum Record ID#.

    As I understand it, I need to create another Summary Maximum field of Record ID# (from Project table? ) do I get this correctly


  • 14.  RE: Compare values with values from previous month

    Posted 09-13-2017 11:27
    Yes, but. See that you need a text field floated up to the Project. So we need to change the steps a bit because you need the Status field from last month also floated up.

    So make that Summary Maximum Record ID field and and gen make a new reverse relationship to have 1history to have many projects. On the right side of that reverse relationship use that summary Maximum Record ID# field.

    Then look up the three fields you need from the history Record up to the Parent.


  • 15.  RE: Compare values with values from previous month

    Posted 09-13-2017 15:55
    Hi Mark, 
    reverse relationship - DONE.
    What I am wondering is the Formula URL button - how to create it?
    Is it a separate Formula URL field, or is it Add Project History button created from the relationship?

    Kind regards, 

    Nenad


  • 16.  RE: Compare values with values from previous month

    Posted 09-13-2017 16:05
    New field of type formula URL.

    Once you get that working we will need to discuss one more thing. 

    Right now the reverse relationship will pull in the most recent update.

    I think that we need to add a filer to that Maximum Record ID# field  so that it does not include where the history record was created during the current month.  That is because it will pull the most current value, and what we want to pull is the previous month's value.


  • 17.  RE: Compare values with values from previous month

    Posted 09-13-2017 16:12

    New Formula URL button created, but when I pasted the formula I keep receiving the following "A variable declaration must end with a semi-colon." .


    Regarding the filter, yup, it makes sense, should that filter be included within relationship definition - Maximum Record ID#? 

    So filter should relate to Project History table, to the field Date Created (not within current month)?

     


  • 18.  RE: Compare values with values from previous month

    Posted 09-13-2017 16:13
    yes.


  • 19.  RE: Compare values with values from previous month

    Posted 09-15-2017 14:03

    Filter is created.

    Did you have time to check what could be wrong with formula?

    Thank you.


    N.



  • 20.  RE: Compare values with values from previous month

    Posted 09-15-2017 15:07
    Please post your formula.   kinda hard to debug without seeing what you are using.


  • 21.  RE: Compare values with values from previous month

    Posted 09-15-2017 15:08
    Please post your formula.   kinda hard to debug without seeing what you are using.


  • 22.  RE: Compare values with values from previous month

    Posted 09-22-2017 15:23
    Hi Mark (sorry for the late reply, I was OOO for the past week), I've pasted it above, but you've probably missed it (I have no experience with java script, so it's all gibberish to me):

    var text URL = urlroot() & "db/' & [_DBID_PROJECT_HISTORY] & "?act=API_RunImport&ID=10;

    "javascript:" &
    "$.get('" & 
    $URL & 
    "',function(){" &
    "location.reload(true);" &
    "});" 
    & "void(0);


  • 23.  RE: Compare values with values from previous month

    Posted 09-22-2017 15:26
    This part need to be changed to below.  after the db/ needs to be a " not a '



    var text URL = urlroot() & "db/" & [_DBID_PROJECT_HISTORY] & "?act=API_RunImport&ID=10;


  • 24.  RE: Compare values with values from previous month

    Posted 09-22-2017 15:42
    like this?
    var text URL = urlroot() & "db/" & NOT [_DBID_PROJECT_HISTORY] & "?act=API_RunImport&ID=10;


  • 25.  RE: Compare values with values from previous month

    Posted 09-22-2017 15:43
    Like this

    var text URL = urlroot() & "db/" & [_DBID_PROJECT_HISTORY] & "?act=API_RunImport&ID=10;


  • 26.  RE: Compare values with values from previous month

    Posted 09-22-2017 15:45
    I did and I receive this message:

    A variable declaration must end with a semi-colon.


    var text URL = urlroot() & "db/" & [_DBID_PROJECT_HISTORY] & "?act=API_RunImport&ID=10; 
    "  javascript:" &
    "$.get('" & 
    $URL & 
    "',function(){" &
    "location.reload(true);" &
    "});" 

    & "void(0);"


  • 27.  RE: Compare values with values from previous month

    Posted 09-22-2017 15:47
    Sorry

    var text URL = urlroot() & "db/" & [_DBID_PROJECT_HISTORY] & "?act=API_RunImport&ID=10";


  • 28.  RE: Compare values with values from previous month

    Posted 09-22-2017 15:52
    done, but unfortunately when I click on that button, it doesn't update the data.
    I've made some data changes clicked on the button, but it doesn't store it :(


  • 29.  RE: Compare values with values from previous month

    Posted 09-22-2017 15:53
    do I need to do the subscription part before or?


  • 30.  RE: Compare values with values from previous month

    Posted 09-22-2017 15:57
    No, it has nothing to do with the Subscription.

    Go to the settings for the app and disable Application Tokens.  That is probably the problem.


  • 31.  RE: Compare values with values from previous month

    Posted 09-24-2017 14:14
    unfortunately that didn't work, I'll try to review all my steps from above with what I've done, and see where the problem is.