Discussions

Expand all | Collapse all

Action If Record Already Exists

  • 1.  Action If Record Already Exists

    Posted 06-05-2020 23:23
    I have a Meetups table and a Check Ins table related to each other, where the Meetups is the parent table.  I am looking to add the following functionality:

    1)  If the Check Ins record does not exist, add the record to the table and send a notification (there's an email address field in another table that is also a parent to the Check Ins table).

    2)  If the Check Ins record already exists, just send the notification (same email address as the #1 above).

    I would like users to be able to click a custom designed button that will take the necessary action but, if the actions need to take place after clicking Save & Close (or whatever), I am okay with that.  I don't know if I need to create another relationship to get a count of the Check Ins and add that summary field to the Check Ins form, or if I should be using one of the API's like API_DoQueryCount and taking action based on that result, or what.  I am interested in the Formula Url and making it a button but, I don't fully understand the structure of the formula for doing this.  The URL Formula for Dummies app only explains but so much.  It would be great if someone gave a detailed example(s) of how to use he URL Formula for each API that is available.  QuickBase's instructions aren't that helpful in this regard.  

    I hope what I am aiming to do is clear and that someone is able to help me figure out how to proceed.  Thanks in advance for anyone who attempts to assist.

    ------------------------------
    James Carr
    ------------------------------


  • 2.  RE: Action If Record Already Exists

    Posted 06-06-2020 01:25
    Update:  I have managed to get results using API_DoQuery and, from what it looks like, API_DoQueryCount.  My dilemma, though, is I am getting a link that is taking me to an XML page.  I don't know how to capture the resulting count from API_DoQueryCount in my Formula Url field.  The next step, of course, would be to Save and Send a notification or just simply send a notification depending on whether or not that value is greater than 0.  I am still open to taking a completely different direction from using the API_DoQueryCount call, if someone has a viable suggestion for me.   I will keep digging and will advise if I get any more results before someone else helps me produce a solution.  Thanks, again (and in advance), for any assistance.

    ------------------------------
    James Carr
    ------------------------------



  • 3.  RE: Action If Record Already Exists

    Posted 06-06-2020 08:00
    I am not understanding your desired workflow. Are you saying that the user is entering a check in record which may or may not be duplicate? Is that the beginning of this process?   For now leave aside all the technical stuff and it just explain your workflow.


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



  • 4.  RE: Action If Record Already Exists

    Posted 06-06-2020 12:55
    Sorry.  I have to remember that I have to approach my questions differently.  Here's the situation:

    Member adds a Check In -> Check In does not already exist -> record is added and a notification is sent to the Member with details for accessing the virtual class

    Member comes back (for whatever reason) to Check In again because he/she needs the details again so....

    Member adds a Check In -> Check In already exists -> another record is NOT added; a notification is sent to the Member with details for accessing the virtual class

    ------------------------------
    James Carr
    ------------------------------



  • 5.  RE: Action If Record Already Exists

    Posted 06-06-2020 20:18

    At EMPOWER they demoed you're approach. But that as just directional and won't be coming for a year. 


    so what we do Quick Base style is to create a Parent Record in a new table called Member Meetings. Set the key field to be hyphen delimited concatenation of the Record ID of the Meeting and the Member. 


    Make afield there called 
    Member Meeting Exists?  With a formula if true  

    make a relationship back down to check in's with calculated  reference field

    Look up that check box field and now you know if there is a parent. 

    maintain the master table with an Automation  

    initialize the master table off a summary report from checking  



    So yes quite a few steps and if you know what you're doing you can do it in about five minutes but at least you can know that QuickBase recognizes that it would be nice to do this without having to create a relationship and look up fields to make this happen



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



  • 6.  RE: Action If Record Already Exists

    Posted 06-06-2020 20:49
    Thanks, Mark.  Good to know I started thinking down the correct path for a "QuickBase Style" solution.  Then I started thinking about a Formula URL and using one of the API's (maybe API_DoQuery or API_DoQueryCount?) so, I started exploring that a little while.  I would like to learn how to utilize that aspect of QuickBase anyway.  For the sake of getting it done sooner than later, I will like take your recommended approach but, I am definitely interested in the Formula URL approach.  I see resources like the Magic Buttons app and the Formula URL for Dummies app, but nothing breaks down implementation of such things in enough detail from what I have seen thus far.  I have seen a couple of things I could probably piggy back off of but, it would be nice to be able to implement a solution that I am also able to explain.  

    At any case, thank you, again, for your assistance.  If you have any recommendations on other resources for learning how to utilize API calls / Formula URLs, I will gladly take them.  Oh, and yes, I have seen the QuickBase API Guide.  It's only but so helpful for me.

    ------------------------------
    James Carr
    ------------------------------



  • 7.  RE: Action If Record Already Exists

    Posted 06-06-2020 23:18
    Wow, Mark!  You are the mastermind behind the URL Formula Buttons for Dummies app?!  Great work on it!! <rounds of applause>  For the specific API's you covered in your examples, you explain things wonderfully.  It would be awesome if the API reference page from QuickBase had your level of detail and examples for each API.  Unfortunately, it doesn't help me with other API's but, it helped get me on the path.  

    At any case, I have figured out how to build the URL for the API_DoQuery (and API_DoQueryCount).  Now, I am trying to figure out how to run with the results (particularly for the count).  If I could capture the resulting count of records, for example, then I could take action from there.  So, if API_DoQueryCount > 0, then edit a record and send a notification, otherwise, add a record and send a notification.  Let me know your thoughts, when you get a chance, and whether or not you have an example of utilizing the API_DoQueryCount that I can look at.  Thank you in advance for your time (past, present, and future).

    ------------------------------
    James Carr
    ------------------------------



  • 8.  RE: Action If Record Already Exists

    Posted 06-07-2020 08:17
    URL formula buttons are not able to listen for a response.

    However, a Pipeline has the potential to do that, except that at present,  a Pipeline can only fetch JSON and to my knowledge is not able to parse the response from an API.

    i believe that you can write your own JavaScript code pages which can listen for responses, but I don't have that skill nor do I have any desire to learn it.  I have enough to do in my low code no code world.

    The API guide has example for each API call, and I their are a few other tips I can post here for you in making URL formula buttons.  I will do that maybe later tonight, as I have some cycling to do now.


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



  • 9.  RE: Action If Record Already Exists

    Posted 06-07-2020 08:43
    Thanks for the response. Interesting that the Formula URL doesn't listen, although, not surprising now that you mention it. I understand not messing with the code page too. I am definitely game for any tips you take the time to provide for Formula URLs. Perhaps, I can come up with an adjustment to the workflow to accommodate what I am looking to do. Enjoy your cycling. Thanks, again.

    ------------------------------
    James Carr
    ------------------------------



  • 10.  RE: Action If Record Already Exists

    Posted 06-07-2020 17:48
    I can't help you with code pages..


    Other tricks of the trade for URL formula button.


    this will execute a single API and refresh the page. It's useful because it will work on any page, it's like a windows F5 refresh.

    var text URL = ... a singe API goes here;


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


    Similarly, the following will do a quiet green fade away pop message for 5 seconds without disturbing the screen.

    var text URL = ... a singe API goes here;
    "javascript:" &
    "$.get('" &
    $url &
    "',function(){" &
    "$.jGrowl('This Item has been put on PO CANCEL snooze', {life: 5000, theme: 'jGrowl-green'});" &
    "});" &
    "void(0);"


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



  • 11.  RE: Action If Record Already Exists

    Posted 06-07-2020 21:07
    Thanks for this.  Can you also add any general information on how to utilize the API's that are designed to return a value as opposed to performing an action (like the DoQueryCount or GetNumRecords)?  Once the call is made, how do you actually capture / utilize the resulting value?  Nevermind the app I am working on.  As I said before, I would like to learn how to utilize this aspect of QuickBase in general.

    ------------------------------
    James Carr
    ------------------------------



  • 12.  RE: Action If Record Already Exists

    Posted 06-07-2020 21:15
    Edited by Mark Shnier (YQC) 06-07-2020 21:15
    Maybe others can chime in on that.  All I know is that that requires the use of javascript code pages.

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



  • 13.  RE: Action If Record Already Exists

    Posted 06-11-2020 00:54
    Just wanted to advise that, after many trials and tribulations with a javascript solution provided by Chayce in another discussion thread a year or two ago, I have figured out how to utilize the API_DoQueryCount in my formula URL field.  I am rethinking my design approach for my button and am now looking to incorporate the API_EditRecord call.  Currently, my URL for this is:

    URLRoot() & "db/" & Dbid() & "?a=API_EditRecord&apptoken=" & $thetoken & "&rid=" & [Record ID#] & "&_fid_42=xxx (where xxx is a variable value in the script)

    Field 42 is a numeric type of field.  My current question is, can I update this URL so that it will increment whatever value is in Field 42 by 1?  If so, how would I do that? I have tried ....API_EditRecord....&_fid_42=fid_42+1 and various similar modifications of this but, nothing I can think of produces the expected result.  At one point, I got the value 42, even though a 1 was in the field prior to clicking the button.  

    If this is not possible in the URL, I have another design approach to take.  It'll just be more elaborate than what I am currently thinking to do.  Any help would be appreciated.  Thanks, in advance, for your time and input.

    ------------------------------
    James Carr
    ------------------------------



  • 14.  RE: Action If Record Already Exists

    Posted 06-11-2020 01:33
    Nevermind.  I figured it out.  Where I am declaring my text variable to call the API_EditRecord, I am ending the string with a reference to the field I want incremented and then incrementing it by 1 right then and there.  So, my URL now looks like:

    URLRoot() & "db/" & Dbid() & "?a=API_EditRecord&apptoken=" & $thetoken & "&rid=" & [Record ID#] & "&_fid_42=([# of Check Ins] + 1)

    Cool.  Now that I am here, I will have the script updated to call the API_DoQueryCount is greater than zero (will probably substitute this with API_DoQuery returning a match), then edit the record and increment the # of Check Ins by 1, otherwise, Add the record (which will have the # of Check Ins set to 1.​​  Notifications will be setup to send to the email address of the corresponding record that was either added or modified.  All of this will happen with the click of my soon to be "Check In" button.  

    Whew!  Time to call it a night and pick this up another time.  Thanks, anyway.  :)

    ------------------------------
    James Carr
    ------------------------------