Lookup table dataprovider - Stop relational data being retrieved?

[conorw]

I have a lookup table defined as an internal BB table:
"Service": lists all possible services, used for dropdown selection on some screens (see image below).

When the service is selected in the drop down it is added to the parent record when the parent record is saved.

However, when retrieving the list of services to populate a dataprovider (and combo box), the query returns the list of services AND all the relational data for each service (in my case this amounts to ~1000 rows)

Is it possible to only return the simple list (without relational data) or am I misusing relations completely?

[melohagan]

Hey @conorw

Is the concern of returning all of the relational row ids a performance concern?
The internal BB database is a NoSQL CouchDB instance, and should handle a lot of data.

If you're finding the app is running a bit sluggish, then you could try creating and using an intermediary table.

• One Service -> One Attendance Link
• One Attendance Link -> Many Attendance

Therefore when you return services, you would only have a single row link, rather than thousands. The compromise may be a potential redesign of your screens to handle the new relationships.

[conorw]

thanks for the reply @melohagan , the concern is definitely a performance concern.

At the moment the app is very sluggish (~10secs until I'm able to select from the comboboxes) when I try to populate 2x combo boxes (1 for a list of services and 1 for a list of users/people).
Essentially, the 2x queries combined are returning most of the entire database.
At the moment there are only 1000 rows total in the DB, but the performance will decrease as the number of rows continues to grow.

This also affects editing a record in the admin UI. If I edit a "Person" record, the related attendance combo needs to load all of the attendance records in the DB (~10sec) before the "Person" can be saved. This delay will continue to grow as the number of attendance records grow.

Your suggestion sounds interesting but I don't understand how you would implement this, what would the columns be in the intermediary tables?

_If you're finding the app is running a bit sluggish, then you could try creating and using an intermediary table.

One Service -> One Attendance Link
One Attendance Link -> Many Attendance_

[melohagan]

So Service and Attendance Link would each have a column for the relationships. (Similar to the current Service -> Attendance you have setup)

Then the Attendance Link table would have a second relational type column for the One -> Many Attendance

[conorw]

@melohagan thanks for your patience, but I'm not sure I'm still fully understanding.
If an attendance record contains a date, service and a person, would i need to create 2x extra intermediate tables e.g. AttendanceServiceLink and AttendancePersonLink ?
If an attendance was updated or deleted would I need to update/delete the rows from both tables?
If I wanted to show a list of attendances would I be able to show the name of the person and the service they attended?

Given the above, do you reckon it would be easier for me to remove the relational aspect altogether and save the data denormalized?

[melohagan]

@conorw I had a go at implementing a linking table.

STEP 1 - DATA

Setup four tables with the following relations:

• One Person -> Many Attendance
• One AttendanceGroup -> One Service
• One AttendanceGroup -> Many Attendance

STEP 2 - SERVICES SCREEN

Have a services screen, that has a link for each service to view those in attendance:

For the URL, we want to pass the AttendanceGroup id and the name of the service:
/attendance/{{ Service Table.Service.AttendanceGroup.0._id }}/{{ Service Table.Service.Name }}

In the /service/new/row screen you will want to save the related AttendanceGroup as well:

STEP 3 - ATTENDEES SCREEN

The attendance screen will show the list of attendees for the chosen service.

Set the route to match the parameters passed from the services screen:

/attendance/:groupid/:servicename

The top-level of the tree will have a AttendanceGroup data provider filtered on the group id from the URL:

The text for the service headline can be pulled from the URL: {{ URL.servicename }}

Next add a repeater Attendance Group and underneath another data provider for the attendees.
The data source will grab the attendees via the relational link of the AttendanceGroup:

This Attendees Data Provider can then be used as a source for the list of attendees table. The design may look a little strange with multiple tables, but when running, the groupid from the URL will filter that down to the single list of attendees for that particular group / service.

Finally define an action for the 'Add Attendee' button.
'Navigate To': /attendance/{{ URL.groupid }}
The groupid will be needed so we know which group to add the new attendee to.

STEP 4 - ADD NEW ATTENDEE SCREEN

Set the screen route to include the groupid as follows: /attendance/:groupid

Now add a form with an options picker for selecting the attending Person, as well as a date picker for the date.
The Field names must be set and match the Attendee table, so Person and Attend Date in this example.

Additionally, add a data provider for the AttendanceGroup (once again filtered on the URL groupid).
This will be needed for our save button.

Finally, 'Define actions' for the Save button. This will have two save row actions.
The first is a simple save to the Attendance table from the form:

The second we want to update the AttendanceGroup (via groupid passed in the URL) and update the attendees:

And the JavaScript:

let group = $("Group Data Provider.Rows")[0];
let attendees = group.Attendees;
if (attendees) {
attendees.push({
	_id: $("Action 1.Saved row")._id
});
} else {
	attendees = [{
	_id: $("Action 1.Saved row")._id
}];
}
return attendees;

---

This may not be the exact use case you're going for, but hopefully it gives you some ideas for how you can improve the performance of your app!

App Export:
attend-export-1649416837174.txt

[conorw]

thanks @melohagan , examples like this really help to see how things could/should be done. much appreciated.