AnsweredAssumed Answered

finding matches and totaling related fields.

Question asked by mcemond on Oct 17, 2014
Latest reply on Oct 22, 2014 by mcemond


finding matches and totaling related fields.


Hello again, I'm looking to do something that seems straight forward but once I get into it I am finding myself more confused. I want to look at a list of checkboxes that have been selected, go back to their original table and pick up some related info on each, list and total it on the original screen. 

Here is my data structure:
Table: "workOrder" Relevant Fields: "secondaryCrew" text, "secondaryCrewRateTotal" number, "employeeName" text.
Table: "crewByService" Relevant Fields: "crewName" text, "billingRate" number.
Both tables are related via crewName/ employeeName.

Ultimately here is a gui snapshot of what I want to create on the work order layout:

Primary Crew:             Rate:
John Smith                  $54
Secondary Crew:      [ Add Crew Popover Button ]*
Jim Jones                    $35
Steve Stone                 $43
                                    Total Rate:
* The add crew button popover contains a checkbox set - Display data from: workOrder::secondaryCrew and a value list for crewByService::crewName 
The GUI is set up as such:
The GUI Layout is based on the "workOrder" table, it is related to the "crewByService" table.

Primary Crew:            Rate:
<<crewByService::crewName>>      <<crewByService::billingRate>>
Secondary Crew:
<<workOrder::secondaryCrew>>   <<crewByService::bllingRate>> ?
Total Rate:

So, in a nut shell, while creating a new work order record, I am adding additional crew to it and want to list in the work order record the relevant rate information. 
Currently: the checkbox lists the crew on the main layout just fine. I also added a button to initiate the script that would calculate all the rate info (its the only way I could think to do it). I tried scripts using "self" as well as trying variables with if workOrder::secondaryCrew = crewByService::crewName then add crewByService::billingRate to the variable.  Nothing I've tried is really working out. I have a feeling I came at this all screwy from the start but I had pre-existing conditions I was contending with. Any suggestions would be great. Thanks! Mike