Discussions

Expand all | Collapse all

Intro & Queries - Warner Bros. International TV Production

  • 1.  Intro & Queries - Warner Bros. International TV Production

    Posted 24 days ago
    Hello everyone.

    We have recently signed up with Quickbase to offer the platform as a online production tool for our 15+ production companies around the world. All our producers enter in all production details and we then compile in the database and provide slate reports whilst also offering a transmission calendar.

    However, I'm not that up to scratch with formulas so wondered if anyone could help me with these tasks. 

    Task 1 - Creating a New/Update field which will show the status NEW with green background when a new record is created in the past 30 days from report creation. OR show the status UPDATE with orange background when a new record is updated in the past 30 days from date of access. These will show in a new field which will then be able to print on the reports. The flags do not.

    Task 2 - Create a report formula which lists all of the records where a specific field has been updated and create a report with basic record detail and then provide that record detail next to the specific field which has been changed with the detail in that field.

    Task 3 - I have a Notes On Progress field set with Log the edits to this field, and show them on forms option turned on am using the formula Trim(Left(NotLeft([Notes],"]"),"[")) in another field to trim off all the other tracked notes when running reports, but I need a formula which if there are no other updated or edited notes, it shows the original note OR the updated note. Can someone help?

    Any help greatly appreciated.
    Thanks, Adam


    ------------------------------
    Adam Hollman
    Exec Director, IP Management,
    Warner Bros.Int.TV Production.
    ------------------------------


  • 2.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 24 days ago
    Hi Adam, welcome and glad you are part of our amazing community. 

    For Task 1, you could make a status multiple choice field and formula rich text field. You would have to update the status field manually or, write a formula, and/or use Automations to determine what is New and what is Updated. You may want something more automated if you have alot of users and/or reports. 
    (Formula Rich Text)
    var text mystatus = [Status];
    
    Case($mystatus,
    "NEW", "<div style='color: #fff !important; background-color: #2ca01c; display: inline-block; border-radius: 4px; padding: 0.5em 1em .5em .75em; margin: 0.5em; border: 1px solid #2ca01c; text-decoration: none;'>NEW</div>",
    "UPDATED", "<div style='color: #fff !important; background-color: #FFB749; display: inline-block; border-radius: 4px; padding: 0.5em 1em .5em .75em; margin: 0.5em; border: 1px solid #FFB749; text-decoration: none;'>UPDATED</div>")​


    ------------------------------
    Everett Patterson
    ------------------------------



  • 3.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 22 days ago
    Adam,
    Can you post these two questions below as separate questions on this forum and for each try to better explain them with an example?    I'd like to help but not able to understand either one.

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



  • 4.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 21 days ago
    HI Mark
    Thankyou for spending the time to read and try and work out my quandry...


    TASK 1

    Creating a New/Update field which will show the status NEW with green background when a new record is created in the past 30 days from report creation.

    OR show the status UPDATE with orange background when a new record is updated in the past 30 days from date of access. These will show in a new field which will then be able to print on the reports. The flags do not.


    So I need a formula so that a column returns a status of NEW or UPDATED if the Record Created field is 30 days old or if the Record Modified field is 30 days old (to show UPDATED)

    Thanks to @Everett Patterson above I know how to create a colour field but I don't know the formula to create the NEW or UPDATED marker.

    Thanks Adam​

    ------------------------------
    Adam Hollman
    ------------------------------



  • 5.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 21 days ago
    if you are referring to those tags at the left side of the record on a report for new and updated, they cannot be manually set by any formula or API.  They are also a bit mysterious as to what causes a record to be considered new or updated for any user - note that these flags are user dependent as if a user comes into an app after being away from it for a month there will be many records flagged as new - for them.

    So, either forget about trying to control them or just disable that feature for the app or else make your own fields if you field you know what definition you want for new and updated.

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



  • 6.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 21 days ago
    I think I've still go the idea in my head of a formula which runs from the standard Date Created and Date Modified fields. So:

    If Date Created is less than 30 days old show 'NEW' or If Date Modified is less than 30 days old show 'UPDATED' and if neither, keep blank.

    And then work on another field to create a formula based on that field with colours etc.

    ------------------------------
    Adam Hollman
    ------------------------------



  • 7.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 21 days ago
    TASK TWO:

    Create a report which lists all of the records in the database where a specific cell has been updated over the past thirty days by using the Record Modified field and create a report with basic record details but showing the specific cell which has been updated.

    Basically, is there a way the quickbase platform can identify fields which have been updated?

    Thanks, Adam


    ------------------------------
    Adam Hollman
    ------------------------------



  • 8.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 21 days ago
    You can see who in future its better to post one question per post as the questions and responses get all tangled up when there are three separate questions.

    The high plan levels of Quick Base has native full audit logging capability on a limited number of important fields in any table.
    But if you are not opting for that feature  in your plan, then you need to do your own logging.  One very low tech solution for a poor man's auto trail is to set up a notification to fire the default email notification into say a gmail account setup for that exclusive purpose.  The form will highlight  changed in red, but will not tell you the old value, just the new value. 

    The Custom email Notifications can also be set up using the field name like [old.Scheduled Date] and that will show the old value of the [Scheduled date] field.  Again, that can be send into a gmail account to allow searching 

    The other alternative is to use Automations to write out a child record to the main record to show the changes to specific important fields.  So, you need to think though which fields are important to track and how much money you want to spend for the native Audit Logging feature (knowing that it's really designed more as an Audit / compliance tool and not so much to easily expose the history to regular users)  or how much effort you want to go to to log key fields.  if you want to log changes to key fields in a child table, then we can carry on that discussion on best practices there.

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



  • 9.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 21 days ago
    TASK THREE - I have a Notes On Progress field set with Log the edits to this field, and show them on forms option turned on am using the formula Trim(Left(NotLeft([Notes],"]"),"[")) in another field to trim off all the other tracked notes when running reports, but I need a formula which if there are no other updated or edited notes, it shows the original note OR the updated note. Can someone help?

    For example, the notes on progress cell shows:

    [OCT-18-19 xxxx@warnerbros.com] Following the rating's success, we're working on a primetime edition now

    Commissioned.

    And I only want the last comment below to be added to a field, and not the original comment of 'commissioned'
    [OCT-18-19 xxxx@warnerbros.com] Following the rating's success, we're working on a primetime edition now

    Thanks, Adam


    ------------------------------
    Adam Hollman
    ------------------------------



  • 10.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 21 days ago
    So it looks like you only enabled logging after some comments were entered as the "Comissioned" entry has no logging as who who made the entry or when. 

    Also do you have the new entries set to log at the top of the list?  Normally they default to show at the end of the field.  Obviously the formula needs to be different if you want to hive off the first entry at the top of the logging as opposed to the last one.

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



  • 11.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 21 days ago
    Yes only after I imported the database in, oh hindsight!

    And yes, new comments are at the top. There is always a line break between new and old comments if that helps.

    ------------------------------
    Adam Hollman
    ------------------------------



  • 12.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 21 days ago
    try this

    Left([Logging at the top],"\n")

    The "\n" is the delimiter for a line break / carriage return, so that says to do a packman type gobble from the left up until the first carriage return.

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



  • 13.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 21 days ago
    Perfect. This is wonderful, thankyou so much.

    ------------------------------
    Adam Hollman
    ------------------------------



  • 14.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 19 days ago
    Mark, coming back to you on this formula, what if I want to remove the brackets and the date/name tag from the latest comments formula please?

    [OCT-23-19 xxx.xxxx@warnerbros.com]

    ------------------------------
    Adam Hollman
    ------------------------------



  • 15.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 19 days ago
    np, try this

    var text LastEntry = Left([Logging at the top],"\n");

    Trim(Right($Last Entry, "]))

    That should packman gobble form the right up to the first ].  The trim gets rid of any extra white space (blanks).


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



  • 16.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 19 days ago

    My apologies I received a syntax error on that one. I copied in the total formula and there was an error on the logging at the top part, and the ($Last Entry part, and then the last "])) part.

    Thanks again.

     

    var text LastEntry = Left([Logging at the top],"\n");

    Trim(Right($Last Entry, "]))






  • 17.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 19 days ago
    Its always best to post the complete error message along with your actual code to help us debug.

    But I can see that there was a mismatched quote on the last line


    Trim(Right($Last Entry, "]"))

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



  • 18.  RE: Intro & Queries - Warner Bros. International TV Production

    Posted 19 days ago

    Mark – many thanks once again