How can I use a formula field to create a table based on the data in a record?

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

A record will have several image files and each image file is tied to fields for latitude, longitude, timestamp, and distance from location X. I'd like to organize this data in table format. I've been trying unsuccessfully to integrate google's visualization table API into QB. 

https://developers.google.com/chart/interactive/docs/gallery/table?hl=en

https://www.quickbase.com/up/bh28xuxfk/g/r3d/eg/va/Google%20Charts%20Results.png

I have yet to successfully integrate any API and I think I'm missing something really basic about the code format. Can anyone point me in the right direction?

Photo of Taylor

Taylor

  • 52 Points

Posted 4 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
I don't understand what the images and geo coordinates have to do with integrating with Google's Visualization Table but here is a working example:

Google Visualization Table
https://haversineconsulting.quickbase.com/db/bj84pfy6m

Test.html
https://haversineconsulting.quickbase.com/db/bj84pfy6m?a=dbpage&pagename=test.html

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=450
Photo of Taylor

Taylor

  • 52 Points
Thanks so much for your reply! My follow up questions are probably very rudimentary so I'll apologize in advance.  

Part 1: I can get your test html to populate on my dashboard via the "webpage widget" but how are you able to save an html file in Quickbase?

Part 2: My ultimate goal is to have a table displayed within a single record for the purpose of displaying that record's data in a more visually friendly format. Is there a way to have the table displayed within a record (as opposed to the dashboard)?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
>Part 1: ...

I don't understand what you are saying here . Are you saying you can run my demo which places a user defined page on the dashboard or have you implemented this in your own application? You seem to be saying the later but this would imply you know where to save html pages in QuickBase. 
FWIW, you can save and edit user defined pages by navigating as follows:

Home | Settings | Pages.

>Part 2: ...
I often times just answer the question a user asks assuming there is a good reason for approaching the problem in the manner the user describes. But I don't really understand why you want to use the Google Visualization library just to display a table - most people use it to generate custom graphical charts. While it is possible to embed just about anything into a QuickBase report using script I am not sure placing a Google Visualization table inside a standard QuickBase report is going to look as nice and tidy as you think.

Irrespective of how you might implement it, what is the overall goal of what you want to achieve?
Photo of Taylor

Taylor

  • 52 Points
1. I can take your link (https://haversineconsulting.quickbase.com/db/bj84pfy6m?a=dbpage&pagename=test.html) paste it in the "webpage widget" and produce your same table in my dashboard. Now that I know how to create a code-based dashboard (thanks!) I can use the actual code.

2. I really just want the record's data to be displayed as a table. It doesn't have to be google visualization, but I wasn't having any success creating a table in a formula text field using <tr> and <td> type tags. Is there a more basic way to create a table that will display nicely?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
> I really just want the record's data to be displayed as a table.
QuickBase limits the HTML tags you can put into a text field with some HTML allowed but you can use the various table tags (eg <table>, <tr>, <td>). So if all you want to do is jam a bunch of fields into a compact table format and place it in a single field you can write your formula as indicated in the following:

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=453

Without testing it there might be some minor display features you want to improve such as borders and spacing but you can easily add CSS to the markup in the above formula.
Mistake: QuickBase does not allow table tags so you have to use script to get the tags into a field. I am short time today so I will update with a solution this over the weekend.
Photo of Taylor

Taylor

  • 52 Points
Thanks Dan!

I was successful in implementing the new image onload technique procedure outlined here (https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=294)

I'm assuming that in place of the "alert" command I need javascript code for a table. This is what I currently have in my UDP:

function create() {
    var x = document.createElement("TABLE");
    x.setAttribute("id", "myTable");
    document.body.appendChild(x);

    var y = document.createElement("TR");
    y.setAttribute("id", "myTr");
    document.getElementById("myTable").appendChild(y);

    var z = document.createElement("TD");
    var t = document.createTextNode("cell");
    z.appendChild(t);
    document.getElementById("myTr").appendChild(z);
}

And this is what I have in my Formula-text field:

"<table>" &
"  <tr>" &
"    <td>" & [AC or Heat Pump]& "</td>" &
"    <td>" &[AC or Heat Pump]& "</td>" &
"    <td>" & [AC or Heat Pump] & "</td>" &
"  <tr>" &
"  <tr>" &
"    <td>" & [AC or Heat Pump]& "</td>" &
"    <td>" & [AC or Heat Pump]& "</td>" &
"    <td>" & [AC or Heat Pump] & "</td>" &
"  <tr>" &
"</table>"

I don't really expect the javascript code to be functional, but it'd be helpful to know if I'm on the right track as far as the page needing javascript coding for a table. I'm assuming once I get that code correct my formula field will display something other the exact text displayed above (e.g. <table><tr><td>AC</td>....etc. )