API Docs & Tools Blog Help
How to manage Relationships between tables in QuickBase for IPP? - Intuit Developer Community Blog

March 17, 2009 | admin

How to manage Relationships between tables in QuickBase for IPP?

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.

Comments

View all
Load more comments