11 Replies Latest reply on Oct 26, 2010 12:41 PM by WinstonChurchill

    Performing Finds.

    WinstonChurchill

      Title

      Performing Finds.

      Post

      I'd hoped to solve this myself, but after spending all weekend and a bit more trying numerous approaches, I need help....

      'table_level' stores data about each floor in a building, 'table_zone' stores data about zones on a floor.

      I built a layout based on 'table_level' using a portal to record related 'table_zones' data, this works well and I'm quite happy with it (it needs to be easy to enter data on the iPad, which it is).

      However a requirement of my accreditation is that I submit copies of my records for audit in a prescribed format and one of those requirements is to provide a printed list for each 'level' with the related 'zones' listed under the basic 'level' info. Because I use a portal, printing such a list is truncated using the layout I created for the iPad, so I looked at creating an additional list layout based on 'table_zones', with the basic 'table_level' data inside the layout header.

      I've discovered enough to know that if I do a find using basic text entry using the same text as I would find in the  'level_name' field (ie. 'L1", which appears in both tables), that the list of 'table_zones' is reduced to only the 'zones' on that 'level' AND the related data in the header from the 'table_zones changes to the same level, which is exactly what I want to happen.

      My next step was to create a third related table, with a 'search_level' field as well as the related field and create a single record. I placed the field on the list layout with a conditional value list based on the 'table_level' records using the 'level_names' field.

      I thought I would be then able to create a button with a script that used the content of the 'search_level' field as the criteria for the 'perform find/quickfind', but just can't get it to work.

      I think my basic problem is that I just don't understand scripting/calculations enough. 

      So along with perhaps helping in this particular scenario, I wondered if there was a dummies guide to scripting. The user documentation and tutorial don't tell me enough and the online scripting/function reference file is just over my head at this time. Similarly I'm also following the VTC tutorial which seems to jump from very basic stuff to well over my head stuff with nothing in the middle.

        • 1. Re: Performing Finds.
          philmodjunk

          I can't quite picture from your post what this third table is, how you've related it to the others nor its purpose.

          • 2. Re: Performing Finds.
            WinstonChurchill

            I may be working backwards a little. My first aim was to have the list layout (using the 'table_zones'), function (from the users prospective) the same way as the form layout (using the 'table_level'). i.e I would navigate through 'table_level' records and have the 'table_zones' records below change accordingly.

            I couldn't figure a way to scroll through the 'table_level' records in the list layout as a related table (rather than a current table), so I contemplated the following compromise:

            Add a search field to the list layout that would use a drop down conditional value list to display the name of the level I wanted to search and then have a script grab that text and perform a search of the 'table_zones::zone_name' field, and then set the related fields in the header (table_level) to the matching records. Not sure about the third table, I suppose I was using it as a safety net, while I tested what I could do.

            • 4. Re: Performing Finds.
              philmodjunk

              If you are looking at records that link to the same table_Level record, then simply placing them in a header part will work and your script can be set up to perform a find for all Table_zone records with the correct matching key field. No need for a third table for this.

              If you want to look at a report that looks like this:

              Table level 1 info
                 Table zone 1
                 Table zone 2
                 etc.
              Tabl3 level 2 info
                  Table zone 8
                  Table zone 9
                  etc.

              That can also be done, but you place the Table level fields in a sub summary and sort your records to group them by table level.

              • 5. Re: Performing Finds.
                WinstonChurchill

                I'm not doing so well at explaining, I don't think. It's the scripting, I am hopelessly lost with:

                I've added the list layout to the post, in case it helps. The previous photo was of the form layout.

                'List View

                My initial goal was to use the arrows at the bottom of the layout to navigate the level records at the top and have the appropriate zone records in the middle bit change as I did so. I couldn't manage to do that - hopelessly lost with script for the little arrows.

                My compromise was to add the 'Display Level Z0' to the header (where 'Z0' is from a conditional value list based on the level records previously created), so that I could choose another level and then press the search button to it's right to have it perform the search (find) and change all the data appropriately. I couldn't manage to do that either. Although I did manage to script the button using text (ie. "Z0"). (Sorry, I used 'L0' in my previous examples, I thought it might make things easier, however procedure requires me to use the nomenclature 'Z0', as I have done in the photo)

                The 'Z0' is from the 'table_level::search_field' and the 'table_zones' field that contains the equivalent 'Z0', is 'table_zones::level_names.

                • 6. Re: Performing Finds.
                  philmodjunk

                  That's pretty close to what I thought you wanted.

                  The top rounded rectangle would be placed in your header along with everything down to the line just below your column headers ( zone, description, building type....) and the lower list of fields would be placed in the body.

                  This would be a list view layout and yor buttons in the bottom can be placed in a footer and scripted to perform finds and sorts to pull up the zone records for the next/previous level.

                  • 7. Re: Performing Finds.
                    WinstonChurchill

                    The top rounded rectangle would be placed in your header along with everything down to the line just below your column headers ( zone, description, building type....) and the lower list of fields would be placed in the body.

                    Everything is where you suggest.

                    This would be a list view layout and yor buttons in the bottom can be placed in a footer

                    Yes it's a list view layout and the arrow buttons are in the footer, for the avoidance of doubt, the footer top is just above the lower corners of the largest dark grey rectangle, above which is a large Trailing Grand Summary so that when viewed at a fixed window size on the mac or printed to a pdf, the appearance from top to bottom is uninterrupted.

                    It appears I have everything in place, but whether I use the arrows in the footer or the search button in the header, I simply don't know how to write the script for either, I've spent all weekend trying various combinations.

                    • 8. Re: Performing Finds.
                      philmodjunk

                      Grand Summary? I don't quite follow all the details of that. In layout mode, you should see:

                      Level/Floor
                      Height   Area this level  Area all

                      Zones
                      Zone  Description  Building Type Activity...

                      [Header]-------------------
                      [ZoneFld][DescriptionFld][BuildingTypeFld]...
                      [Body]--------------------------
                      [Left Button] Level 1 of 1 [Right arrow]
                      [Footer]-------------------------

                      The details of the scripts for your buttons depend on your data so it's difficult to layout specifics.

                      The key is determining what record in the Table_Level table is the "next" or "previous" level.

                      Let's say your levels are numbered sequentially. Here's possible script to find the zone records for the previous level:
                      Set variable [$PrevLevel ; Value:  Table_Level::Level - 1]
                      Enter Find Mode []
                      Set Field [Table_Level::Level $PrevLevel]
                      Set Error Capture [on]
                      Perform Find[]
                      Sort [No dialog ; Restore ]

                      A global field at the top of your layout can be used to specify a level name so that you can search by name with a very similar script though you won't need a variable when you use a global field like that. 

                      • 9. Re: Performing Finds.
                        WinstonChurchill

                        Grand Summary? I don't quite follow all the details of that.

                        It's possibly not relevant but I've added a Grand Summary (quite a large one) that contains the graphics between the last list item and just above the lower curves of the large dark grey rectangle (Top of the footer). Because the header and footer remain static in list view, without the Grand Summary I see two options, neither which seemed satisfactory.

                        1. Have a regular-ish size footer, then if there were only a couple of records in the list, have a gap down to the footer on the pdf print and on the mac at a fixed window size.

                        2. Have a very large footer that fixed the gap issue in one, but printed only a few lines on each page when exported to pdf.

                        Hopefully, a layout photo might help

                        Layout view

                        Zones
                        Zone  Description  Building Type Activity...

                        [Header]-------------------
                        [ZoneFld][DescriptionFld][BuildingTypeFld]...
                        [Body]--------------------------
                        [Left Button] Level 1 of 1 [Right arrow]
                        [Footer]-------------------------

                        That's more or less how it is apart from the Grand Summary Thing, so in context I'd say I've got:

                        Zones
                        Zone  Description  Building Type Activity...

                        [Header]-------------------

                        [ZoneFld][DescriptionFld][BuildingTypeFld]...

                        [Body]--------------------------

                        [Left Button] Level 1 of 1 [Right arrow]

                        [Grand Summary]-------------

                        Nothing

                        [Footer]-------------------------

                        Let's say your levels are numbered sequentially. Here's possible script to find the zone records for the previous level:
                        Set variable [$PrevLevel ; Value:  Table_Level::Level - 1]
                        Enter Find Mode []
                        Set Field [Table_Level::Level $PrevLevel]
                        Set Error Capture [on]
                        Perform Find[]
                        Sort [No dialog ; Restore ]

                        It seems we are looking now at utilising the next/previous buttons in the footer as the overall solution.

                        I see your script, I will work through it and try to understand it and apply it, however I have no idea why or how I would have chosen that script or what it means at this stage, I'd like to learn, but i'm struggling to find a starting point at this time. 

                        • 10. Re: Performing Finds.
                          philmodjunk

                          Actually the same basic scripted search method can be used with your search field at the top of the form. You can use set field while in find mode to copy criteria from a global field where the user entered the criteria into the relevant field or fields.

                          Using a large trailing grand summary would reduce the number of rows visible when you print or PDF the layout. I don't have a mac to test this on, but don't think you need it here. The footer should remain at the bottom of the screen in browse and should print on the bottom of the page when printing/previewing/Saving as PDF without using any such trailing grand summary part.

                          • 11. Re: Performing Finds.
                            WinstonChurchill

                            I tested the pdf print and yes it leaves a gap, so I've redesigned the layout without a big grand summary.

                            I also noticed that the found set is sorted 'Z2/1, Z2/10, Z2/11, Z2/2.... and not Z2/1, Z2/2, z2/3....., which I managed to resolve by having the zone new record auto enter calc generate a leading zero for single digit zone numbers after the '/' (ie Z2/01, rather than Z2/1), although I'd have preferred to have the sort, sort without leading zeros.

                            I also picked up that the script you suggested didn't take account that the level was named Z2 and not just 2 for example. I managed to vary the script to sort that out.

                            I also managed to vary the script to stop it searching for records below Z0 and above the highest level for which I have records. This retains the user impression of scrolling through records rather than just searching for them.

                            However I'd be interested to know why changing the second line of your script to:

                            Set variable [$PrevLevel ; Value: "Z" & Right ( table_level::level ; 1 )  - 1], did work, while:

                            Set variable [$PrevLevel ; Value: "Z" & notLeft ( table_level::level ; 1 )  - 1], didn't work.

                            Because, while I haven't tried it, I don't think the version that works now, will work for Z10 and above.