Relationships and totals

[cschmitz]

I have a table for repair orders. The repairs can be flagged as Open, In Progress, Complete, or Refused.

 

I have another table that called Main Menu. It's layout holds the main navigation buttons and portals showing various information for all of the different tables in our system. One of the portals on the main menu is a listing of open repairs. The relationship between the repair table and main menu table is:

 

Main Menu Repairs

StaticID = StaticID and

ActivityOpenID = ActivityOpenID

 

Where StaticID in both tables is set to 1, ActivityOpenID in the main menu is set to 1, and ActivityOpenID in repairs is a calculation that says If (RepairStatus = "Open"; 1;0)

 

That part works fine. The portal shows a listing of all repairs flagged as open.

 

 

What I wanted to do is create a summary field in repairs that is the Total of Repairs::ActivityOpenID so that I can always display a live total of open repairs on either the Main Menu layout or the Repairs layout. I created this field, but since we have so many records in the Repairs table (12000+) the database has to pause to recalculate the summary field every time you go from a found set to showing all records.

 

Can anyone think of a better/faster way of calculating a total for the open repairs or am I just stuck??

[Ender]

There's an easier way. Instead of using a summary field in Repairs, use an aggregate calc in Main Menu:

 

count(Repairs::RecordID)

 

 

Also, your relationship is a bit convoluted. It's a little easier to use a global filter in the Main Menu for the RepairType (maybe even allow it to be changed by the user), matched with the RepairStatus:

 

Main Menu Repair=

Main Menu::gRepairType = Repair::RepairStatus

[cschmitz]

There's an easier way. Instead of using a summary field in Repairs, use an aggregate calc in Main Menu:

 

count(Repairs::RecordID)

 

 

Also, your relationship is a bit convoluted. It's a little easier to use a global filter in the Main Menu for the RepairType (maybe even allow it to be changed by the user), matched with the RepairStatus:

 

Main Menu Repair=

Main Menu::gRepairType = Repair::RepairStatus

 

 

The aggregate calculation worked like a charm. Also, I took your advice about the relationship. I did create the global field and setup the field as a popup list using the status value list. It works beautifully. This was a great solution to both get the data quickly and condense all of the information into one portal instead of several separate portals.

 

Thank you thank you!! The help is much appreciated.

[Ender]

Glad to help.