3 Replies Latest reply on Oct 22, 2014 6:34 AM by mcemond

    finding matches and totaling related fields.

    mcemond

      Title

      finding matches and totaling related fields.

      Post

      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:
                                          $132
      * 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:
      <<workOrder::secondaryCrewRateTotal>>

      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

        • 1. Re: finding matches and totaling related fields.
          philmodjunk

          Are you saying that your relationship is:

          WorkOrder::EmployeeName = CrewByService::CrewName

          The field names do not suggest to me that they actually hold the same data. Do you have one record for each "Crew" that is named after one employee?

          And matching records by a person's name is not ideal as names are not unique, subject to data entry errors and people change their names.

          • 2. Re: finding matches and totaling related fields.
            mcemond

            My Original response is below, but I thought maybe I could be more specific in my question:
            If I were to attach a script to a button that does the following, what would that script look like?
            Click Button > 
            check the layout's table wrokOrder::secondaryCrew (checkbox set who's value is populated from a value list of crewByService::crewName), 
            If this field has names that have been checked, go to crewByService::crewName (a related table),
            Enter find mode and constrain found set based on those checked names.
            In that found set, total crewByService::billingRate and save it to workOrder::secondaryCrewRateTotal for the record we were initially on.

            Then for good measure, make sure we clear out that found set so it doesn't affect future runs. 

            Thanks!
            Mike 


            Original Response...
            Hi
            Thanks for your response. actually when creating a work order "workOrder", I have a hierarchical value list so first you select a crew member (CrewByService::CrewName) then you can see what services that employee can do from the same related table (CrewByService) Then it is stored in the work order table as the workOrder::employeeName (each work order has a primary guy that is assigned to it). So that's kind of a separate task but explains the origin of my mess.

            What I'm trying to do now is add additional members from that same CrewByService::crewName, store them with the current work order (workOrder::secondaryCrew) and grab their related rate data from the CrewByService::billingRate and add it all up so that the person creating the work order can see what this is going to cost (per hour).

            To answer your question simply, no, they don't really. CrewByService::crewName is a listing of all the names of the crew members (one per record). workOrder::employeeName hold the single "primary" crew member responsible for doing the work associated with the work order. workOrder::secondaryCrew is (at least in current attempts) a listing of any combo of the crewByService::crewName (via a value list and checkbox set).

            Hopefully that adds a little clarity. My brain is having a hard time with this sitting in front of it so I can imagine what it must sound like via this post. 

            Thanks,
            Mike 

             

            • 3. Re: finding matches and totaling related fields.
              mcemond

              Hi
              I decided to re-build my entire crew section from the tables up to make the above work better. The relationships have all changed and I'm using a portal in stead of scripts. So no need to continue down this road. 
              Thanks
              Mike