How to?: Query two postgreSQL TABLES, filter to create and update a single combined TABLE

[PaulRossAclear]

Edit: Edited the Topic and comments to clarify the distinction between table and database.
We have been able to connect two postgres data bases but only able to query one database at a time using a screen tool. Need assistance with query multiple database and filter for specific results. Once the desired records form each database have been identified, would like to create a combined database that will also be a postgres database. Most helpful then would be to create a unique identifier for each combined record in the new database preferably a randomly generated rather than sequential. This can be manually managed. eg. user queries both databases, selects desired results and commits to new database with unique randomly generated serial so that in the new database these records are tied together. It may be helpful to have the source databases appended with this unique id so that they can be identified as having been "matched" and can be found in the new database.

More background:
The concept is to tie multiple records together for a subject. ie. for analysis of real estate. A home may be sold several times and those listings may be recorded on a MLS service and a tax records database. The MLS records and the tax records may not match and often the tax records lag behind the MLS, so depending on the date of collection the data may not yet be posted. The MLS also may be recorded as sold when in fact it did not. The transaction may be worth analysis but it should be identified correctly. They may also have been listed for rent at various times so these records need to be recorded and tied into a search. More functionality and features are planned but this most basic step of creating the database of tied records, we feel is the most critical element.

[melohagan]

Hey @PaulRossAclear

If the tables are in different databases, then naturally there's not going to be a convenient way to join these tables, as far as I'm aware.

What you may need to do is form "soft" relationships where you have an ID field in one table that matches the primary key of the other, and then you can use filters on your data providers to get related rows.
Still I believe it may not be good practice to have relationships between tables in different databases.

[melohagan]

Is there any reason you couldn't create the combined table ahead of time, and insert rows into it as needed, rather than creating it via a query in Budibase?
If you just needed to combine the data for the purposes of viewing it, you could use a query with a JOIN?

[PaulERayburn]

On further consideration, I think the tables may not need to be merged.

The goal is to search multiple tables for various fields criteria to identify records related to the same property. For example, we will have multiple sales of a given property in both MLS and Tax Records. (Not all properties have PID's or PUD's so we want to create our own).

The idea is to manually search and validate these records and then identify them all with a common ID so that for future analysis the validation is already complete and anytime a property is identified in a subsequent search which has the ID assigned is known to be validated. From this stage we would need to be able to add those to another table or export for further analysis for a specific assignment. In our case, they will be analyzed using R via Rstudio so a "plug" into this table(s) may be possible, minimizing exporting and enhancing the ability to update/ revise searches.

Some of the complicating factors are that we need to identify the source of the data.

In my case, I have chosen to add the prefix TX to all tax records field names which would allow the user to identify that source. The MLS records have a unique MLS# tied to every record with the exception that the active records will become duplicates in subsequent searches which are added to the database as they will have the initial "Active" status then "Sold" or "Withdrawn etc.". We could add the prefix ML to each of those fields for consistency. We will have additional sources of information which will be stored in another table(s) to be queried and identified to the "property" in the same process as described.

So we do not want to combine or merge fields. For this reason, the suggestion of pre combining the tables may not make sense. I have tested this and of course, it is possible in a CSV file however very cumbersome. (pasting the field names to the end of the row of one source and then pasting the data below the last row of cells for the initial data set and so on for additional records. Excel may offer some automation to do this so I may need to dig into that a bit further but I am hoping this can be managed with a CRUD/APP interface.

One other consideration I have is to avoid confusion subsequent searches may find new records to be added to the set. We need to determine whether to add the prior ID to the new record or create a whole new ID for the entire new set. That could be handled either way whichever is easier.

[PaulERayburn]

I have attempted to query two tables in BB using forms, tables and field filters as an option. Unless there is another method there is some redundant steps such as entering search fields twice. The next step would be to assign the common unique ID to a set. Then find a way to export or access the tables with the new ID's. Heres a screen shot of where I am at.

[PaulERayburn]

So we're still working through this and I'm just trying random tests. I have no clue how this is expected to work at this stage but I tried creating a button to update rows with a unique ID created with a javascript. But it just crashes the browser with a memory when attempting preview

and heres the java script for unique id which appears to function in a javascript editor
Here's a jsfiddle to demonstrate: http://jsfiddle.net/wSQBx/

here's the code from this https://stackoverflow.com/questions/10726909/random-alpha-numeric-string-in-javascript

function randomString(length, chars) {
var result = '';
for (var i = length; i > 0; --i) result += chars[Math.round(Math.random() * (chars.length - 1))];
return result;
}
document.write(randomString(32, '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'));

I'm shooting in the dark here. Any ideas?

[melohagan]

Hey @PaulRossAclear

Just to double check, your JavaScript binding is returning the random string, e.g.

function randomString(length, chars) {
    var result = '';
    for (var i = length; i > 0; --i) result += chars[Math.round(Math.random() * (chars.length - 1))];
    return result;
}
return randomString(32, '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ');

[PaulERayburn]

Yes, I verified the code will produce a random number string. My goal is to have it populate the fields of the selected rows but it is just crashing the program.

…

On Thu., Nov. 24, 2022, 12:19 a.m. melohagan, ***@***.***> wrote: Hey @PaulRossAclear <https://github.com/PaulRossAclear> Just to double check, your JavaScript binding is returning the random string, e.g. function randomString(length, chars) { var result = ''; for (var i = length; i > 0; --i) result += chars[Math.round(Math.random() * (chars.length - 1))]; return result;}return randomString(32, '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'); — Reply to this email directly, view it on GitHub <#8734 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A4DALENQCQSKGXOQ7XXEZV3WJ4QKFANCNFSM6AAAAAASFNTQBU> . You are receiving this because you commented.Message ID: ***@***.***>

[melohagan]

Ah I just remembered that Math.random will cause Budibase to crash. Here's a related discussion: #5203

[PaulERayburn]

I just tried that code

function uuidv4() {
return ([1e7]+-1e3+-4e3+-8e3+-1e11).replace(/[018]/g, c =>
(c ^ crypto.getRandomValues(new Uint8Array(1))[0] & 15 >> c / 4).toString(16)
);
}

return uuidv4();

and it does not appear to function in a javascript but that is where my lack of coding skills is an issue. Is there something missing? Do I need to add something?
So it doesnt appear to function in a javascript editior
image

and when I try it in place of the math.random it still crashes BudiBase running in chrome. I have not tried other browsers.
image

[PaulERayburn]

Apologies, I see I responded to the wrong thread so I'm just going to post an image of your response here so we don't lose this thread.

So assuming we can get the random number generated, I am still not clear on how I can get that into the table(s).
For clarity, that needs to be one random number generated and then added manually or automatically into the field columns for the selected records(rows).
Thanks for your patience with me on this.

[melohagan]

You could possible try adding a formula column, that uses that code as a JavaScript binding value: https://docs.budibase.com/docs/formula

[PaulERayburn]

I don't understand how that would solve the issue of creating an id to tie together multiple selected rows. This solution appears to only create a unique ID for every instance or row which would not solve anything.
Maybe I am misunderstanding.
Thanks again for your assistance.

[drearystate]

If you are trying to search 2 databases at once you can use a form insert dataprovider and data and within that data provider and data have a second data provider and data (2nd database to search/filter through. the form can use form fields to filter results with both. I do this with looking for vehicles, I use 1 database to filter the other. When binding I use "like" instead of "equals" to help with spelling errors from others as well simple syntax errors.

[PaulERayburn]

So to be clear when we say database I think we are referencing tables. These tables could be one each in a separate database or multiple tables in one database. But if you check my setup I think it was close to what you are mentioning.

I was using Starts with for PID and then Like for address. I could not get my searches to filter both tables and had to filter each table separately. Then the only option I could get to work was export CSV for each table. I would then need to either manually upload these sets to a new database table or create some kind of automation.

[drearystate]

Yes, and every field within that form can be used to filter any child database from the parent as well as the parent DB/table (data provider).

[drearystate]

In form, outside of the data providers at the top of the hierarchy put your filtering fields there. This is how mine looks. i added the filtering fields to the top of the 2nd data provider to filter everything below it. I know you cant see it but theres about 3 other data providers they filter below those.

[drearystate]

If you are wanting to merge the 2 databases then within the form mentioned above have action buttons alongside your tables, have the action fill out empty form fields on the screen (hide them if needed) and 1 final button that takes current fields and creates new database. It becomes a 3 click solution but it at least merges your databases.

[PaulERayburn]

That sounds promising. I'd be OK with the extra 3 click solution. Do you think it's possible to update the source database(s) with the Parent Child ID for identified sets? I think in this manner once sets have been identified, subsequent searches of thise databases would reveal that sets/ matches have been made and can be easily identified. If the source database/ tables did not store those ID's the new database/tables with the "sets" would have to also be referenced. There may be an easy way and I may be over/under thinking that.

[drearystate]

Yes, I would add a new column to each DB that when you click the first button on each DB item it puts the same id in each so that they would be identical.