Display value in Cell from different query

I have a column within a table that shows a value "key" from Database 1. That "key" value is also present in Database 2 and I want to introduce a new column that shows the "status" of each "key" value".

May idea was to create a query "getStatus" to retrieve each status per key. But how would I make this happen?

Here is my ressource query:

select "Key", "Status"
FROM "vulcanTickets"

Any help is highly appreciated.

Hello @Christian_Liebig

You can join both tables using join in your query.

Is it a different database or a different table? With a different table you can best use a join in your query.

SELECT v."Key", v."Status", t."Key", t."Status"
FROM "vulcanTickets" v
INNER JOIN "Table2" t ON v."Key" = t."Key";

If it’s a different database, then it depends what database. And how many results there are per key and how you want to display this data.

Can you tell us more what the schema is of database1 table and database2 table?

Can you make an example what you want as result?

  1. You could get all keys from database2 and then run the query of database1 on success of database2
  2. transform the data of database1. Use map() to iterate the records
  3. For every record in map() you can use find(key) on the data of database2 to return the value of the status column.

Someone here can write the code if you provide more details of the tables and how you want to use it. :innocent:

@ZeroCodez I didn’t see your reply. I was still typing :sweat_smile:

Thank you for your replies so far.

To give you more details:
I am building a web app that basically provides the agenda for a JourFixe with my PO. The use case is that I need a tool / app that keeps track / displays status and progress of issues that are living in 2 different systems:

  1. I have "cases" living in Salesforce that are opened from my internal stakeholders. Everything that we need 2nd and 3rd Level support for, we escalate those issues using
  2. Jira, where they exist as "tickets"

So to know which (Salesforce) case is currently "Escalated" (so which case needs to be tracked in the JourFixe) I am regularly dumping / updating the cases into a Retool Database table "SF cases". This DB table holds the following fields:

The field "vulcanLink" is (as of now) editable and is the reference to the 2nd Retool Database "Vulcan Link" with these fields:

In my table component I now want to "combine" output from these 2 retool Databases. Relevant are those 2 columns:

image

"vulcan link" is the editable column and in "Vulcan Status" I want to fetch the status per "vulcan Link" (which is the "Key" field in the DB).

I hope, this helps

Ensure that the primary key from the sf_cases table is present in the vulcan_link table as a foreign key when joining both tables.

SELECT 
  sf.image AS sfImage,
  sf.caseId AS caseId,
  sf.caseName AS caseName,
  sf.vulcanLink AS vulcanLink,
  vl.status AS vulcanStatus
FROM 
  sf_cases AS sf
LEFT JOIN 
  vulcan_link AS vl
ON 
  sf.vulcanLink = vl.key
ORDER BY 
  sf.caseId;
1 Like

You’re saying two databases. But what you mean is one retool database with 2 tables. These can be joined by the query @ZeroCodez posted.

If your tables are from different databases, you’ll need a different approach than this query.

1 Like

For the second case mentioned above with combing data from different resources, one option is to use our Query JSON with SQL query type

1 Like

:exploding_head: learned something new. Thanks!

giphy

1 Like

Hi @ZeroCodez,

I'm afraid I don't really understand the task to introduce the foreign key. You are saying:

Ensure that the primary key from the sf_cases table is present in the vulcan_link table as a foreign key

BUT:
There is a 1:n relationship from the primary Key ("Key") within the vulcanTickets table. And not vice versa. So that means, that in theory 1 "Key" can be linked to multiple "Case Numbers" from the SFCases table.

Note:
I have tried to set up the primary Key from "vulcanTickets" table as foreign key within the "SFCases" table (so basically the other way around) but run into the following error:

Ignore - this is now working

Thank you so much!

How did you get it to work? :ok_hand: :smile: