1 2 Previous Next 16 Replies Latest reply on Jan 14, 2013 2:37 PM by hanstrager

    Help to make calculation that determains how many times a category is selcted in portal

    hanstrager

      Title

      Help to make calculation that determains how many times a category is selcted in portal

      Post

           I have a portal, where i have a button that can add records and another one that can delete the records again.

           in each record you can choose a category, fx fabric, lining or leather.

           next to the category is there a number (currently all showing no.1).

           Is it possible to have that number change to fx. fabric no. 1 when you add the first fabric and then lining no. 1 if thats the next record, and if third record again is fabric the number will this time turn into 2... and should you then delete fabric no.1 fabric no.2 will then turn into fabric no.1..?

           So basicly im looking for some help so my portal knows based on the category what number it has to put in the field...?

            

            

      Picture_10.png

        • 1. Re: Help to make calculation that determains how many times a category is selcted in portal
          philmodjunk

               Define an auto-numbered serial number in the portal's table. I'll call it __pkMaterialID.

               We'll call the portal's table "Materials" and link it to a table called "garments" in this relationship:

               Garment::__pkGarmentID = Materials::_fkGarmentID

               This is just to set up the portal you already have. Your names will differ from mine.

               Add a new occurrence of Materials, MaterialsSameCategory and link it to Materials:

               Materials::_fkGarmentID = MaterialsSameCategory::_fkGarmentID AND
               Materials::Category = MaterialsSameCateogry::Category AND
               Materials::__pkMaterialID > MaterialsSameCategory::__pkMaterialID

               This relationship matches to every record in your portal of the same category that was created prior to it plus the self same portal record.

               Now, Count ( MaterialsSameCategory::__pkMaterialID ) define to compute from the context of Materials, will return the following values for the portal example you've posted:

               1 (first fabric record)
               2 (2nd fabric record)
               1 (1st lining record)
               3 (3rd fabric record)
               2 (2nd lining record)
               1 (1st Leather record)

          • 2. Re: Help to make calculation that determains how many times a category is selcted in portal
            hanstrager

                 im really not sure about the set up... do I link materials and garments together by __pkMaterialID? or is that a complete seperate field..?

                 I've uploaded my realationship graph. My portal is on technicalsheet 2 and the portal is taking records from line items.

                 Is "materials" my "technical sheet 2" and  "garments" my "line items"...?

                 and where do I put Count ( MaterialsSameCategory::__pkMaterialID )..?

                  

                 sorry for my inexperience...

            • 3. Re: Help to make calculation that determains how many times a category is selcted in portal
              philmodjunk

                   garments = technical data sheet and materials = line items.

                   Looks like you'll need to add an auto-entered serial number field to line items to serve the role of __pkMaterialID. (at least I don't see a field for that in your screen shot.) Since you already have records in this table, after adding that field set up to auto-enter a serial number, put the field on a Line Items layout, show all records and use Replace Field Contents with the serial numbers option to give all your existing records a serial number.

              • 4. Re: Help to make calculation that determains how many times a category is selcted in portal
                hanstrager

                     I think I've done as described above, but it dosen't work. When I add a new record in my portal it adds it in Line Items, but It dosen't visualy show up in the portal.

                     I have throught the relationship I added an extra field to my porta in "technical data", so the number showes up - I asume its  __pkMaterialID that makes the number..?

                     this is the step I haven't done yet because im not sure if it's a step and how to do it:

                Now, Count ( MaterialsSameCategory::__pkMaterialID ) define to compute from the context of Materials, will return the following values for the portal example you've posted:

                1 (first fabric record)
                     2 (2nd fabric record)
                     1 (1st lining record)
                     3 (3rd fabric record)
                     2 (2nd lining record)
                     1 (1st Leather record)

                      

                • 5. Re: Help to make calculation that determains how many times a category is selcted in portal
                  philmodjunk

                  Count ( MaterialsSameCategory::__pkMaterialID )

                       Is the calculation for a calculation field that you define in Line Items. This is the field that gives you the numbers I showed in my example. Without it, you don't have any numbering. Put this calculation field in your portal to show the numbers you requested.

                       When you enter this expression in the Specify Calcualtion dialog, there's a "context" drop down at the top of the dialog box. Make sure that Line Items, not Line Items 2 is selected in that drop down.

                  • 6. Re: Help to make calculation that determains how many times a category is selcted in portal
                    hanstrager

                         It still dosen't show anything up in the portal, no data at all - the portal i just completely blank..?

                    • 7. Re: Help to make calculation that determains how many times a category is selcted in portal
                      philmodjunk

                           Please remove the Garment ID fields from both tables.

                           The Technical data sheet ID field performs the same purpose.

                           Also keep in mind that adding an ID field with an auto-entered serial number does not automatically enter any data into that field for records that already exist in the database. Only NEW records will recieve the auto-entered data such as a serial number. You'll have to update existing records with a serial number value before they contain any data. Replace Field contents can do this update for you if you have not already done so.

                      • 8. Re: Help to make calculation that determains how many times a category is selcted in portal
                        hanstrager

                             Think I found the mistake... it seems to work - almost, just is it possible that the number comes as soon as i Pick a category instead when i add another portal row..?

                             Also when i fx have a fabric in the category and change it to something else - the number doesn't follow...? is it possible to change that aswell?

                        • 9. Re: Help to make calculation that determains how many times a category is selcted in portal
                          philmodjunk

                               The count cannot update until the change is "committed" back to the database. This can require clicking the layout background or a different portal row unless you add a script trigger on the field that performs a script with Commit Records in it.

                               Changing the category should also change the numbers--both in the new and in the old category, but this also will have the same display refresh issues that you are reporting with the first problem.

                          • 10. Re: Help to make calculation that determains how many times a category is selcted in portal
                            hanstrager

                                 sorry my bad it's working, just because my fields are so close you seldom press the background. I've been trying to look a bit into the script triggers just afraid to fuck something up, tried to ad a script trigger on object enter, with script: commit record/request, but didn't go down 2 well... How do I assign a script trigger to the field, since this would really have this thing working perfectly..

                            • 11. Re: Help to make calculation that determains how many times a category is selcted in portal
                              philmodjunk

                                   You might be able to do this with the OnObjectSave trigger:

                                   Right Click the Field while in layout mode and select Script Triggers from the menu that pops up.

                                   Select the OnObjectSave trigger and set it to perform this single step script:

                                   Commit Records []

                                   But with this trigger, the Commit records step will "lose" the focus on a specific portal row. If you are tabbing out of this field, you can end up in a field somewhere else than the next field in the portal row due to the commit records step. Thus, you may find that this works better:

                                   Set Variable [$Row ; Value: Get ( CurrentPortalRowNumber ) ]
                                   Commit Records[]
                                   Go to Object ["PortalObjectNameHere"]
                                   Go to Portal Row [ no dialog ; $Row ]
                                   Go to Field [//specify next field in portal row here]

                                   Go to object isn't necessary if you have only one portal, but I make it a habit to use it anyway so that if I later add a portal to such a layout, it won't interfer with the script. To give the portal an object name, select it while in layout mode and type a name into the "Name" box found at the top of the Inspector's position tab. Then copy and paste from there into your Go to Object script step to make sure that it refers to the exact object name of the portal.

                                   Refresh Window []

                                   is another script that sometimes helps force a screen update. (I sometimes end up testing both steps separately and in combination to see what results work best.)

                              • 12. Re: Help to make calculation that determains how many times a category is selcted in portal
                                hanstrager

                                     tried commit records and refresh window... refresh window didn't do much, so trying option no 2...

                                     in the "Set Variable [$Row ; Value: Get ( CurrentPortalRowNumber ) ]" What do I put in "name" and "value" in the specify tap.

                                     also

                                     "Go to Portal Row [ no dialog ; $Row ]" i only have a drop down menu option with: FIRST, LAST, PREVIOUS, NEXT and CALCULATION. I guess i need calcultation, just not sure what to put into the calculation..?

                                     i can only find commit records/requests, but assume it's what you ment..?

                                • 13. Re: Help to make calculation that determains how many times a category is selcted in portal
                                  philmodjunk

                                       put $Row into name and Get ( CurrentPortalRowNumber ) into value.

                                       Select the calculation option. Put $Row in the specify calculation dialog. Be sure to select the "perform without dialog" option for this script step as well.

                                       Yes, Commit Records/Request[]

                                       is what you want.

                                  • 14. Re: Help to make calculation that determains how many times a category is selcted in portal
                                    hanstrager

                                         when i put in Get( CurrentPortalRowNumber ) it comes with an error saying "this parameter is an invalid Get function parameter"... do I need to put Get(ActivePortalRowNumber)...? If so it dosen't do much difference, I still need to click outside the portal for it to update...

                                    1 2 Previous Next