8 Replies Latest reply on Dec 31, 2014 11:50 AM by scotthoffman

    How to calculate a field based on other "found" records

    scotthoffman

      Title

      How to calculate a field based on other "found" records

      Post

      Setup:

      2 Tables - Main , Cart

      Main::ID = Cart::ID

      This is in a "store front" format for the web. User Creates Main::ID record, chooses item which adds a new record in Cart that is linked to Main. I have a field Cart::Item_ID that i want to calculate the ID based off of Main::ID & How many records there are for Main::ID..

      So IF:

      Main::ID = PSOL100    then    Cart::item_ID = PSOL100-1   or   PSOL100-2     if a record PSOL100-1    exists.

      This would then start over at -1 for the next Main::ID record

      So then the Invoice would show a Main::ID with sub records of Cart::item_ID which will then be barcodes for tracking throughout company.

      Any thoughts, Questions...I'm horrible at explaining so bare with me..

        • 1. Re: How to calculate a field based on other "found" records
          philmodjunk

          My initial thought is DON'T DO THAT!

          With what you describe Main::ID does NOT equal Cart::ID and that causes all sorts of problems for you.

          Define Main::ID as an auto-entered serial number. Define Cart::ID as a number field. Link your tables in a relationship. You can either use a portal or other means to link each new record in Cart to the customer's current Cart record by giving it an identical ID. No letters "POL" nor the -1, -2, values should be included in this field. If you need such values to support requirements other than the basic relationship that you need here--such as "legacy systems" or clients that insist on such a system, use different fields that calculate these values from their respective ID fields to produce those values but don't try to use them to match records in your basic relationship.

          Now your invoice layout can be based on Main and use a portal to Cart to show the items in the customer's cart or you can set up a list view layout based on Cart with fields included from Main. To pull up such a set of records for a single Invoice, you can either use Go To Related Records--starting from the Main layout or a scripted find that finds all Cart records with a specified ID to produce the needed list of Cart items on a List View layout.

           so bare with me..

          Sorry for this, but I can't resist: Since It's Dec 31st and I'm not a member of the polar bear club, I'll politely decline at this time... surprise

          • 2. Re: How to calculate a field based on other "found" records
            scotthoffman

            OK, Thats how i have it set right now. Main::ID = Cart::ID, I also have Cart::item_ID set as auto enter -1,-2,-3..and so on,

            (Cart::ID)PSOL100 & (Cart::item_ID) -1 = PSOL100-1

            BUT, On a new Main Record These Cart::item_ID's continue and dont "reset".

            Thats why i was trying to see if theres something else to set the Cart::item_ID as.

            • 3. Re: How to calculate a field based on other "found" records
              philmodjunk

              OK. You could define a "sequence field" and use a self join relationship to do that as each new cart record is created. (match to all records with the same value in Cart::ID and set it to the value of Max ( Cart|SameID::Item_ID ) + 1)

              Your Item ID then becomes either merge text or a calculation field that combines the value of ID, the "-" text and the value of the sequence field.

              But that can create issues if a customer is adding and then later removing certain items from their "cart".

              I suspect that you have a button that they click to complete their sales transaction. If so, that script can use Go To Related Records to pull up a found set of those Cart records and use Replace Field Contents with the serial number option to assign 1, 2, 3 to that sequence field.

              There are also ways to set up a "count of" summary field with the "running total" option that can also dynamically number the cart records and in some cases, you can just use the layout symbol for the record number.  These options can be a simpler way to go, if you can work within their limitations.

              • 4. Re: How to calculate a field based on other "found" records
                scotthoffman

                Do you see any problems with this...

                In order to add item record they push a button "Add to Cart" in this script i perform a find for Cart::ID , then grab the foundcount ($$foundcount), then it creates the item record, sets the  Cart::item_ID field  to   Cart::ID & "-" & $$foundcount + 1

                • 5. Re: How to calculate a field based on other "found" records
                  philmodjunk

                  So if you have 5 cart items, you want to number them all 6? wink

                  With 5 cart records found, Get ( FoundCount ) returns the value 5--not what you want here from what I read in your prior posts.

                  Replace Field Contents using the serial numbers option would seem much simpler, for one thing, you don't need a loop as you do it all in one script step.

                  • 6. Re: How to calculate a field based on other "found" records
                    scotthoffman

                    No, in my case, it will only add 1 to the foundcount per new record. so if foundcount is 1 the number added will be 1+1(foundcount = 1)

                    When adding items to the cart ,  The customer can only add 1 item at a time(for editing of data being added to item).

                    So far when i run through it, it seems to be working correctly.yes

                    Your right though, this would not work if adding multiple items at a time.

                    • 7. Re: How to calculate a field based on other "found" records
                      philmodjunk

                      Won't that make the first cart record 2 instead of 1? Your found count would be 1 and then you add 1 to it...

                      And will it cause an issue for you if the customer then deletes a cart record when they change their mind and decide not to purchase an item they previously added? Keep in mind that a customer might select 4 items and then change their mind and remove the second item from their cart...

                      I suggested a "Batch" assignment of these numbers only when the customer is ready to "check out" and be charged for their purchases as a way to avoid such possible complications. (But maybe a gap in this number series won't be an issue.)

                      • 8. Re: How to calculate a field based on other "found" records
                        scotthoffman

                        Good point, I'll batch number when they get moved from the cartsmiley