5 Replies Latest reply on Sep 2, 2015 9:47 AM by erolst

    Input a value based on two variables in a table

    JamesDiPaoloSmith

      So I'm working on a database where I need to record tasks that staff members have manually inputed in relation to a project. In order to invoice these tasks I need to assign billing rates to each staff member. However, a staff member's billing rate is dependant on two things: First the client that we bill to, and then to what class they are in relation to that client. For example Staff Member A could bill $100.00 to Client A while he bills $150.00 to Client B, and Staff Member B could bill $120.00 to Client A while he bills $175.00 to Client B, depending on who the client is. I've already set up a table that looks like the image posted below, which differentiates between client values, staff values and individual rates.

       

      Rates.tiff

      I've associated the "Client Value" between the Bill Rates Table and the Clients Table (which is related to the Projects table, because we have one client per project), and I've associated the "Staff Value" between the Billing Rates table and the Staff Table ( which is related to the Tasks Table, because one staff member can complete many tasks). So far I've only been able to either populate a Rates field with one Bill Rate value in relation to the Client, or one Bill Rate value in relation to the Staff Member.


      So how can I fix this in order to automatically populate a field with a bill rate that is determined by both the client billed to and the staff member that completed the task to be billed?


      Note: One of my associates has told me that I should set each staff value as a different field, ie Field 1 is SA, Field 2 is SB. My problem with that is that I then need to associate the Rates table with the Staff table, so which field, SA or SB in the Rates table, do I associate with the StaffValue field in the Staff Table, and how do I tell FM to either associate with SA or SB. If this is possible, then how do I accomplish this?

        • 1. Re: Input a value based on two variables in a table
          erolst

          Your screenshot doesn't show up, but if you have

           

          Client --< Project -- <Task >-- Staff

           

          plus a BillingRate table with id_staff, id_client and rate

           

          create a calculation field cClientID in Task as Client::id (you could also use Project::id_client; either way, the result type must be the same datatype as these two fields), and create a relationship

           

          Task::id_staff = BillingRate_byClientAndStaff::id_staff

          Task::cClientID = BillingRate_byClientAndStaff::id_client

           

          Give Task::billingRate an auto-enter calculation of


          BillingRate_byClientAndStaff::rate

           

          If you also have client-independent rates, create another relationship

           

          Task::id_staff = BillingRate_byStaff::id_staff

           

          and give Task::billingRate an auto-enter calculation of

           

          GetValue (

            List (

              BillingRate_byClientAndStaff::rate ;

              BillingRate_byStaff::rate

            ) ; 1

          )

          • 2. Re: Input a value based on two variables in a table
            bigtom

            I would use ExecuteSQL for this.

             

            ExecuteSQL( SELECT BillRate FROM BillingTable WHERE ClientValue = ? AND StaffValue = ? ..........)

             

            You could use this to pull in multiple staff and bill rates for the same project. If the table is only for reference values this will still be very fast.

             

            I would use a portal for the data entry but it is not the only way.

            • 3. Re: Input a value based on two variables in a table
              JamesDiPaoloSmith

              Okay, however this model allows me to complete what I want IF I set different rates to every individual staff member AND if I choose the Staff Member and Client associated with the Task through a relationship with the BillingRates table. What if two staff members have the same rate? I need a system where I have a set number of rates stored so that I can associate multiple members and clients to a rate class, ie. Client 1 is a Client Class A, Client 2 is a Client Class B, Staff 1 is the Staff Class A and Staff 2 is a Staff Class B, and where I can change the rate of either a staff member or a client by simply associating them with a different Class, instead of having to define only their set rates manually if they do change. Is there a way that I can get the StaffClass and the ClientClass to automatically associate with the StaffID and the ClientID so that I can tell FM something like "On commit of Tasks::StaffID and Projects::ClientID, lookup the value from BillingRates::Rates where Staff::StaffClass = BillingRates::StaffClass and Client::ClientClass = BillingRates::ClientClass" ?

               

              The picture that didn't show up basically looked like the table below. I'm not associating the Staff or Client ID with an "individual to them" ID in the BillingRates table, but with an ID field that I can associate more than one Staff Member/Client to. Is this possible?

               

              Client Class          Staff Class          Rate

              A                              A                         100

              A                              B                         120

              B                              A                         150

              B                              B                         175

               

               

               

              To add to this, is there a way that I can complete this through a lookup or reference function sort of thing, the reason being that once we've billed the client we don't want the rates on the Tasks Table to change for those set records if we change the Class of either a staff member or a client for a future project? I want to be able to go back and definitively say that we billed this guy this amount this many years ago without having to check if the rates on the bill have changed because we've updated them in future projects.

              • 4. Re: Input a value based on two variables in a table
                JamesDiPaoloSmith

                What do you mean by "Where ClientValue = ? AND StaffValue = ?" ?

                • 5. Re: Input a value based on two variables in a table
                  erolst

                  To add to this, is there a way that I can complete this through a lookup or reference function sort of thing, the reason being that once we've billed the client we don't want the rates on the Tasks Table to change for those set records if we change the Class of either a staff member or a client for a future project? I want to be able to go back and definitively say that we billed this guy this amount this many years ago without having to check if the rates on the bill have changed because we've updated them in future projects.

                   

                  Making this an inserted, rather than a referenced value, was already inherent in my original outline.


                  A BillingClass table is just one more level of indirection; you could then use an auto-enter calculation that looks at the BillingClassID of the client and the staff member, then reads that value from the BillingClass table and inserts it.


                  With


                  BillingClass --< Client --< Project -- <Task >-- Staff >-- BillingClass


                  Use the auto-enter calculation


                  ExecutesSQL ( "

                    SELECT Rate

                    FROM BillingClass

                    WHERE clientClass = ? AND staffClass = ?

                    " ; "" ; "" ; Client::BillingClassID ; Staff::BillingClassID

                  )


                  ? is a placeholder that inserts the values from the optional arguments at the end of ExecuteSQL()