8 Replies Latest reply on Jan 5, 2011 10:09 AM by philmodjunk

    Portal calculation

    ultranix

      Title

      Portal calculation

      Post

      This may be easy for you, but here is my case. I have a table of customers and related table, called customers_portal. They all share same customer ID and in that portal I have customers' sales process information, like dates of meetings, time, info and to-do for the next meeting.

      I have two date fields: 1) date of meeting 2) date of becoming a customer. I want to calculate on which meeting a person became a customer. So, basically, when a person becomes a customer, those two date fields show exact same date.

      How do I do this?

        • 1. Re: Portal calculation
          ultranix

          And question #2, related to the same scheme. Whenever I enter any date in "Date of becoming a customer" field, I want it automatically to have auto-entered serial number, which could be Count of existing customer ID's +1. It's script triggers? And how do I set this up?

          • 2. Re: Portal calculation
            philmodjunk

             when a person becomes a customer, those two date fields show exact same date.

            Is DateOfBecomingaCustomer a field defined in customers or customers_portal? I assume it's in customers and that date of meeting is a field defined in customers_portal. If so, you can use a number of different script triggers or a button that creates a new blank portal record and uses Set Field to copy the contents of the first date field into the second.

            CustomerID should be defined as a number field that auto-enters a serial number. You can use field options to specify this for your field and also whether you want the serial number assigned when the record is first created or when it is first committed. That's the simplest, most error free way to do this.

            • 3. Re: Portal calculation
              ultranix

              No, both date_meeting and date_customer are on the same customers_portal table. so how then would i get that count?

              Portal looks like this:

              Date_Meet | Date_Customer | Notes

              2010-12-01|2010-12-01| Notes

              2010-11-11|                   | Notes

              2010-10-14|                   | Notes

              So, in this case, a person became a customer during the 3rd meeting. How would I calculate that? All fields displayed in the portal belong to customers_portal table and this portal is shown in Customers layout, using related data from Customers_portal.

              • 4. Re: Portal calculation
                philmodjunk

                I'd put Date_Customer in the Customer table. From what little I know about your database, it doesn't make sense to define the field in Customer_Portal as it appers this field contains data only one row of your portal. This would greatly simplify the entire issue as you can then set a script trigger using OnObjectExit that creates a record in the portal and puts the date from Date_Customer into Date_Meet.

                 

                • 5. Re: Portal calculation
                  ultranix

                  Why? It's relationship one to many. One customer may have many meetings with him and one meeting (generally) includes only one customer.

                  • 6. Re: Portal calculation
                    philmodjunk

                    You know your database and your procedures better than I. I am assuming that a contact can only become a "customer" one time only. If so, there would appear to be no need to repeat this field over and over in your portal when you can just record this date in a field in the Customer table and thus have the field appear only one time. That's just a suggestion, you may have very good reasons for doing what you are doing.

                    Here's another idea that may simplify things for you. Don't include this date field at all. Since this date field would appear to always be either blank or show the same date as the Date_Meet field, it would appear that what is significant is that this particular portal reocord shows that this is when the contact became an customer. Thus, you might just place a check box field with a single value in its value list that you click when you need to document that the contact has now become a customer. The relevant date would already be recored in the Date_Meet field.

                    PS. now that I've suggested all these alternatives, if you want to keep your design as is, try setting up a script that uses set field to copy the date from Date_Customer to Date_Meet. An OnObjectExit trigger can be set on your Date_Customer field to perform this script.

                    • 7. Re: Portal calculation
                      ultranix

                      I believe I would have to set up a button to simplify things up for that date_customer.

                      Another thing I stated - how to count on which meeting a person became a customer. Can you help me doing that?

                      • 8. Re: Portal calculation
                        philmodjunk

                        Can you given an example of what you want to see here? I don't fully understand what you wanted.