How do we handle QuickBase relationships using the Intuit Partner Platform Flex SDK Plugin?
The QuickBase Missing Manual describes the mechanics for creating relationships between tables as well as common use-case relationships. For example, you can think of a one-to-many relation between projects and tasks where each task in the tasks table is linked to a specific project in the projects table. The projects table is referred to as the Master table and the tasks table as the Details table. QuickBase uses the master's key field or the record id# to relate the records between the master and details table.
Note that within QuickBase the only type of
relationship you can directly define is 1:many though within your application
you can implement a 1:1 relationship by having a back reference many:1
relationship from master to detail and implementing your business logic to
enforce the 1:1 nature of the intended relationship.
Why
implement a relationship in the first place? Quite simply, because once you
have a relationship, QuickBase can do a lot of work for you: fields from the
master can be automatically reflected into the details rows (effectively an
Inner Left Join for you SQL-nuts) so you don’t need to go look up the master
record when you have a detail record in-hand and just need a couple of fields
from the master record on a regular basis; and fields from the details table can
be summarized in the master record using a variety of summary operators
such as Average, Count, Maximum, etc.; they can even be summarized with the
equivalent of a “where” clause to summarize only a subset of the details records
(i.e. total task hours assigned to the current user).
How does this work with the IPP Flex SDK Plugin? Using DTOs and some of the APIs in the Kingussie framework we can simplify the task of adding records, maintaining relationships and refreshing summary fields.
I have a Developers table as the master table and a Ratings table as the details table. There can be many Ratings records for a single developer and the summary field which is the average rating in the Developers table keeps track of this.
The challenges are
How do we add a record to the ratings table and relate it to a developer record?
How do we refresh the summary field of the developer record in the view when a ratings record related to the developer is added? Normal refresh mechanisms such as those implemented by
the QuickBaseDTOArrayCollection will not detect changes to the developer record
if all that changed were details records summarized by the developer record, so
our app needs to “know” that it has changed something that influences other
records and force a refresh.
To add a Ratings record, we first relate it to the Developer record and add it.
rating.populateDTO();
// Relate the Ratings record to the Developers record
model.ratingsDTO.Relateddeveloper = Number(model.developersDTO.rid);
var addRatingEvent: QuickBaseEventStoreRecord = new
QuickBaseEventStoreRecord(model.RatingsDTO,
new KingussieEventCallBack(doRefreshRating));
addRatingEvent.dispatch();
Where rating is a simple IDNForm.
<widgets:IDNForm id="rating" dto="{model.ratingsDTO}">
<widgets:IDNField fieldLabel="Enter Rating:" fieldName="Rating"/>
</widgets:IDNForm>
To refresh the view using the Kingussie event chaining, we define a method doRefreshRating which essentially refreshes the parent record and thereby updates the summary field.
public function doRefreshRating(data:Object): void
{
// refresh the parent record now
var refreshDeveloperEvent: QuickBaseEventRefreshRecord = new
QuickBaseEventRefreshRecord(model.developersDTO);
refreshDeveloperEvent.dispatch();
// refresh the ratings DTO
model.ratingsDTO = new Ratings_DTO();
}
In conclusion, we looked at how to handle relationships between tables, what is a Master table and a Details table, how to link records between the tables and how to automatically refresh summary fields in the parent record.
Leave a Reply