7 Replies Latest reply on Nov 8, 2011 1:22 PM by MikeEdwards

    Searchable List Calc via portal

    MikeEdwards

      Title

      Searchable List Calc via portal

      Post

      I'm trying to a customers history page via a portal. The relationship I've used in the past is through the customer code. However now there are different codes for different locations. I've attempted to use a list calculation of the various codes on the customer history page and link that via portal to the estimating page in a different table. I'm was oping this would pull up the data of all codes on the list. It seems to work with a find on the Cust History page however it does not seem to work on a find via the portal on the estimating page. I'm using FM 11.

        • 1. Re: Searchable List Calc via portal
          mgores

          Ideally there would be a customerID field you could base the relationship on that was independant of the code.

          If what you are doing works in a find, you could probably set up a cartesan relationship to the history table, which would list all records in the history table, then filter it using your find criteria.

          • 2. Re: Searchable List Calc via portal
            MikeEdwards

             Tried the cartesan relationship to no avail.  Is there any to connect a series of fields via a portal?

            • 3. Re: Searchable List Calc via portal
              philmodjunk

              Maybe we went the wrong direction here.

              You have what tables in your system?

              Customers, and Customer History, any others involved in this issue?

              How are these two tables currently linked in a relationship or are they not curently linked?

              When you say you want to see all Customer History records, is that all the records in the table or just those related to the current customer record?

              Once you have this list in a portal, what do you want to do with it?

              • 4. Re: Searchable List Calc via portal
                MikeEdwards

                 Two tables Customers and Customer History. Connected by Customer Code Fields.   Customer History is built around a Single Main Customer Code. Customers was a single code but they now have cust codes that vary slightly because it represents a seperate location. I would like to reference the main code and it's Customer history via portal.  I thought of adding secondary code fields to Customer History and configured a code list which I'd hoped to reference through a table connection. Doesn't seem tom work.

                • 5. Re: Searchable List Calc via portal
                  MikeEdwards

                   There is only a single cutomer history record to be referenced.

                  • 6. Re: Searchable List Calc via portal
                    philmodjunk

                    A return separated list of values should match to any customer history record with one of the values. This set's up a kind of limited "join table" in that one field listing the values, but it does work.

                    Say you have three code fields in your customer record. A forth calculaiton field, cCodeList, could be defined as:

                    List ( Code1 ; Code2 ; Code3 )

                    and then you could link customers to customer history like this:

                    Customer::cCodeList = customerHistory::Code

                    However, if you later need 4 or more codes, you have to modify your database design to support the change.

                    A join table is another option you could use as well and it avoids the need to modify your design to add more codes for a given customer.

                    Customer----<Customercodes----<CustomerHistory

                    Customers::CustomerID = CustomerCodes::CustomerID

                    CustomerHistory::Code = CustomerCodes::Code

                    If you needed 3 customer codes for a given customer, you'd create three records in CustomerCodes, all with the same CustomerID number but each record with a different code. You'd use a portal to CustomerCodes on your Customer layout to manage this list of codes.

                    There is only a single cutomer history record to be referenced.

                    How can there be only one record if you have multiple codes?

                    Shouldn't there be at least one for each code?

                    Won't you have multiple records for the same customer in customer history, each logging a different event (sale, customer service call, etc. )?

                    If not, please explain how this works...

                    • 7. Re: Searchable List Calc via portal
                      MikeEdwards

                       Got it to work. The problem was the calc need to be expressed as text.