BudiBase not Showing Data correctly for linked Tables after Importing Big Chunk of Data

[tinomerl]

Preface
I already updated to the latest version and am now on 2.8.10. I already searched the Issues, Discussion and Google.

Setup
I'm running BudiBase as a Single Container in an Azure Container Instance, according to this tutorial. My Database is a PostgreSQL Flexible Server also on Azure.

I'm trying to setup up BudiBase as a way to make our workflow for data entry easier. Therefore i created a new database in a normalized way, setup BudiBase with test Data and I'm trying now to import the Data. The main use Case will be entry of Ad Spendings, which can't be automatically fetched via API. I import the data via a Python Script into the Database directly

Problem Description
The central table is ad_spendings and contains about 27k rows. I normalized the campaigns, country and source. In the central Ad table they are present as foreign Keys. Normally we have about 35 different countries, 175 sources and 63 campaigns. They are all displayed correctly until i import the data for the Ad Spendings into the Database. Afterwards only 2 values of either campaigns, source and country will be displayed in the BudiBase Backend and on the Screens for these Tables. If I go to the Ad Spendings Screen, I can see the Name of the missing Values displayed correctly. If i click on the entry however to edit it, the Relationship Picker will display "Null" and i can now only select from the 2 which Budibase will show me. The problem seems not to be with the database and the data, as all relations in the database are displayed correctly and all columns have appropriate Foreign Keys.

What I Tried

• Dropping the whole Database and Set it up as fresh
• Importing only chunks of the ad spendings
  • When i do this the data will be displayed correctly until i cross a certain threshold of about 100 Rows
  • But it seems that when i enter the data manually the error does not persist
• Import the data via BudiBase Backend as a csv
  • It will not recognize my foreign Keys
• Deleting the Relationship in BudiBase and declaring it again
  • After deleting the Relationship the data will display correct in the e.g. Country Table. But after setting up the Relationship again the Error is there again
• Setting up a new App with the Database.
• Importing Data via the BudiBase API and the Create Row method
  • Error also happens after a about hundred rows

[melohagan]

Hey @tinomerl

Would you be able to include some screenshots to outline the problem as well please?

[tinomerl]

Hey @melohagan,

thanks for reaching out. Sure.

Database schema

Before Importing Ad Spendings

Country Table
In Budibase

In Database

Source Table
In Budibase

In Database

After Importing Ad Spendings

Country Table
In Budibase

In Database

Source Table
In Budibase

In Database

Number of Rows in Ad Spendings

Taken from Ad Spendings Screen

When i click on one of the Entries to edit it

These are not all, there are still some missing

Does this help? Do you need anything else?

[melohagan]

Hey @tinomerl

Sorry for the late reply, but I'm pretty sure the issue you are seeing is the same as this one: #11386

Budibase does LEFT JOINs when handling relationships, which means if you have more than one relationship per table, the number of rows being queried can quickly expand.

For SQL databases there is a limit of 5000 rows, however you could take a look at the SQL_MAX_ROWS environment variable: https://docs.budibase.com/docs/hosting-settings

With that said, I would recommend using custom queries instead to handle the JOINs yourself if you are running into this problem, as the performance will be better.

[tinomerl]

Hey @melohagan,

thanks for following up! So just to make sure i get this right. Instead of using the table itself as a datasource for my view i should create a custom query for reading, updating and creating rows each right?

[melohagan]

Yes that's right. The downside is that you won't be able to make use of the relationship pickers, you'll have to use options pickers with data providers instead.

https://docs.budibase.com/docs/option-picker#using-a-data-provider

[tinomerl]

Thanks again. I figured it out now. I managed to make it work with just one custom query, for reading only. This one is used for the main Table. The option Pickers gave me a headache but i made it work as described here. So whenever i update a row or create a new one, i just use the regular ad_spendings table for the form and hide the columns for foreign Keys. In this Form i have as many Data Providers and Option Pickers as there are Foreign Keys. The option Pickers have an on Change Action, that updates these foreign Key Fields.