Is it possible to execute a query and return only the most recent ONE of the records that match the query?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Table "A" holds records created each time a notification is sent out by sms. Over time multiple notifications are sent to a recipient (and hence multiple records are added to QuickBase table "A", showing the time the notification was sent, to whom it was sent, etc.). Recipients of the notification may respond via sms at any point, hopefully to the first notification sent, more probably later after several notifications have been sent. When I receive the sms from the recipient, acknowledging the notification that was sent, I need to query the notifications table "A" to determine the time the latest notification was sent, which ostensibly is the one the recipient is responding to. Is it possible to write this query in such a way that it simply returns the ONE most recent record from the notifications table "A"? In MySQL you could simply sort on date and limit your query as "SELECT TOP 1 x, y, z FROM A ORDERED BY DateSent DESC", etc. But QuickBase is not relational db. Is it possible to return just the one record desired?

Photo of rpw49

rpw49

  • 12 Points

Posted 3 years ago

  • 0
  • 1
Photo of Jack

Jack, Champion

  • 50 Points
If you have for example table A parent and table B child

Table A can have many children (Table B Records)

If you want to look at table A and see the most recent record in table B in a field you can do this using a reverse relationship.

Here's how it works. Create a summary field showing the max record ID/Key field in the table to table relationship. Call it max id. then create a lookup field to max id (this passes the value back to the child table).

Then create a new relationship Table b can have many parents Table A (the same relationship but in reverse), and set the reference field to the max id field. 

Once this is created you can create lookup fields of your max child record in your parent.

You can also pass these back to the child record via lookup fields in your existing relationship Table A can have many table B.




Hope this makes sense and resolves your issue.

Jack
Photo of rpw49

rpw49

  • 12 Points
Your answer makes sense, but I ended up just coding it in PHP as follows (partial code of the class):

        $table = "bkx9cxdxj"; // SMS Confirmation Requests table   
        $_phone = ltrim(rtrim($_phone));
        $clist = "3.6.7.8.9.13.15.16.18";
        $format = "structured";

        $query = "6.EX." . $_wko . "[a]8.EX." . $_phone;

        $obj = new QuickBaseQuery($table, $query, '', '', $clist, $format, '', '');

        $this->rows_array = json_decode($obj->json, true);
        $this->times_sent = array_column($this->rows_array, 'Sent At');
        $key = array_search(max($this->times_sent), array_column($this->rows_array, 'Sent At'));
        $this->most_recent = $this->rows_array[$key]; // most recent record as array

I simply return all the rows matching the query (from a class I wrote that returns results in json format) to the instantiated object, then examines the times sent to find the 'max' and uses that as an index back into the array of returned json. The indexed row is the latest confirmation request.
Hi Jake,

can you give me an example in which first we edit a record in child then, update in parent and return to return from where button was triggered.

Regards,
Gaurav.
Photo of Jack

Jack, Champion

  • 50 Points
URLRoot() & "db/" & Dbid ()
& "?act=API_EditRecord&rid=" & [Record ID#]
& "&apptoken=d44v7587hfhf84j4mk4fffght"
& "&_fid_36="&URLEncode(true)
& "&_fid_37="&URLEncode(false)
& "&_fid_39="&URLEncode(true)
& "&_fid_45="&URLEncode(false)
& "&rdr=" & URLEncode(URLRoot() & "db/buyjnbbgf?a=API_AddRecord&apptoken=bx245675843gbjfubxt7m4zv6r&_fid_49="&URLEncode([Link ID])
& "&_fid_36="&URLEncode([Price])
& "&_fid_38="&URLEncode([Net])
& "&_fid_39="&URLEncode([Tax])
& "&_fid_35="&URLEncode([Product])
& "&_fid_52="&URLEncode([Vendor])
& "&_fid_32="&URLEncode([Purchase method])
& "&_fid_37="&URLEncode([Price Line])
& "&_fid_53="&URLEncode([Supplier name])
& "&_fid_40="&URLEncode([Foreign Currency Unit])
& "&_fid_41="&URLEncode([Foreign Currency Amount])
& "&_fid_54="&URLEncode([Link ID])
& "&_fid_58="&URLEncode([Record ID#])
& "&_fid_69="&URLEncode([Reverse Lookup ID])
& "&rdr="& URLEncode(URLRoot() & "db/bihnh5j854"))


Gaurav, heres an example of editing a record and then adding a record in another app and redirecting back to a dashboard. This gives you the structure and code needed to achieve what you need.

Just remember what ever you are using as record id's or key fields to identify the record must already exist unless using api add record or the call will error.
Thanks Jake :)

I have already fix my issue.
Can you please have a look on this questions ?

https://quickbase-community.intuit.com/questions/1377093-can-we-add-a-record-in-different-application-from-button

In this screnario, i can successfully add a record in 2nd app but it does not redirect back to Ist App from which dashboard it was triggered.


Regards,
Gaurav Sharma