11 Replies Latest reply on Mar 5, 2013 1:45 AM by paolobkk

    Sorting portals in IWP for list

    paolobkk

      Title

      Sorting portals in IWP for list

      Post

           Hi Everyone!

            

           I'm developing a database for IWP for the first time and I'm using portals to avoid the limits of  the list and table view.

           (FMSA12 on Macmini Server)

           A simple example is the ITEMS table.

           I've created another occurrence of the ITEMS table (ITEMS2) and related to each other with a X operator (using item_id)

           The portal display all the items in list as I want, and this is ok.

           Now I would like the user to be able to sort the portal but I didn't find a solution yet. frown

           Is there any way to script a sorting relationship or portal? (eg. 1 button for category, 1 button for code, 1 button for description, an so on)

            

           Thanks for your comments!

      UPDATE:

           I've found a temporary solution:

           I've defined a global field (gfield_for_sort_IWP) and a calculation field (field_for_sort_IWP - unstored) as follow:

      Case ( 
            
      gfield_for_sort_iwp = "code" ; code;
      gfield_for_sort_iwp = "description" ; description;
      gfield_for_sort_iwp = "category" ; category
      )
            
           Then I set in the relationship to sort records by field_for_sort_IWP.
            
           The scripted buttons in the layout set the gfield_for_sort_IWP with "code" (or other x value), and the calculated field updates with the values I want to use for sorting.
            
           This works also if the sorting is done directly form the portal.
            
           In FMA12 client the sorting is pretty fast (990 records over fix IP), but in IWP from the browser it takes sometime to relist the items.
            
           I'm wondering if there is another solution or some fine tuning to do to speed up.... any suggestion?

        • 1. Re: Sorting portals in IWP for list
          jmw

               As field_for_sort_IWP is an unstored calculation, it will be recalculated each time it is needed which can be a bit of a burden in IWP. So if you can make the field a stored calculation you might see an improvement. If other tables are involved you can also think of making the field a number- or text-field and repopulate the values when needed using f.e. a Replace Field Contents script-step with the calculation. 

          • 2. Re: Sorting portals in IWP for list
            philmodjunk

                 One option to consider is to put a tab control with a panel for each sort order. Put a copy of your portal on each tab and then specify a different sort order for each copy of the portal. The user then "sorts" the portal by selecting a different tab in the tab control.

            • 3. Re: Sorting portals in IWP for list
              jmw

                   I might be wrong in this, but a possible drawback of having the same portal on different tabs (although differently sorted) might be that if some fields of the portal are being edited, the layout might contain two instances of that field with different values. FileMaker warns against that, so I try to avoid such a situation. Now, that might not be an issue as tabs are being used, I am not sure about that. 

              • 4. Re: Sorting portals in IWP for list
                philmodjunk

                     That shouldn't be a problem as all three portals will reference the same data source table and (usually) the same table occurrence. Plus, the tab panels will keep the user from seeing more than one copy of the portal at a time.

                     I'd experiment with both options to see which works best for IWP. The original method will require that a script use commit records to update the portal each time the user specifies a change in how the portal should sort and this could lead to unacceptable delays.

                • 5. Re: Sorting portals in IWP for list
                  paolobkk

                       Thank you for all the suggestions!

                       I've tried all the possibilities and I had to drop the "field_for_sort_IWP" for 1 simple reason that I didn't keep in mind. As this database is in IWP and is shared I can't let 30 users replace or modify the same field over 900 records at the same time...

                       Another thing is that I'm not allowed to make a stored calculation with it because the calculation includes a Global field.

                       I've also tried to use "Replace field contents" in a looped script but it was taking an eternity to update all the records in FMP12 , I can see the bar showing 2 record every second update, (in IWP I had to quit after waiting for 15 minutes).

                        

                       Working with the portals as PhilModJunk suggested is a good solution for me but the actual sorting time it became longer than before.

                       With FMP12 client I note a tiny lag while switching the tabs (totally trascurable) but in IWP the time has increased quite a lot.

                       When I go to the layout it takes up to 1.5min to show the layout (with the tabs and the sorted portal) and every time I switch the tab, it can take between 1 and 2 minutes to reload (it seems it's resorting it every time I click the tab).

                  However I've notice that the quantity of fields on the row is quite important in IWP. Every row has 13 fields, I've tried to use only 1 and the time was reduced to 5 seconds when switching tabs!

                       All the fields are text or number from the same table (simple fields, no calculations, no related)

                        

                       As last option I've also tried to give a table occurence to every portal and sort the records from the relationship. It didn't seems to improve, opening the layout the first time was just insanely long. In the client I could see the sorting bar.

                        

                       Other tries for improvement without succes include limiting the portal to 10 raws (it was 20) but didn't change anything.

                       PS: I'm testing in a momentary Internet connection 10Mbps/0.50Mbps so perhaps the speed is giving some problems. I will update more info with other Internet connections.

                        

                  • 6. Re: Sorting portals in IWP for list
                    jmw

                         You say you tried Replace Field Contents in a looped script; is that necessary? The script step raplaces the field contents for all records in the found set, so if you use it with the portal-table as a destination it will acces all these records using only this one step, no loop necessary. In fact if there is one it will handle each record not just once but as many times as there records, an eternity seems a fitting duation for that kind of work....  

                          

                         As to 30 users editing the same field:  if "field_for_sort_IWP" is a global field it will be unique for every user individually. So even though it is the same field in name, every user has it's own instance of that field, meaning they can all have different sort orders. Almost like they are all different fields and not the same field at all. But maybe I am missing the point of your remark here.

                    From your results it seems that sorting the table takes some time, and maybe having multiple occurrences on portals multiplies that time if FileMaker prepares the tables anyway for display regardless of the displayed tab. And that will then also go for sorting through relationships. 

                    As that time seems to be long, is that also the case without any sorting at all? If so, you might want to have a look at the reasons behind that. Any calculations (or parts thereof) which can be stored yet are unstored, unnecessary indexing, those sort of things. For example; if the calculation of "field_for_sort_IWP" contains parts that can be stored, consider placing these in seperate stored calculations and have "field_for_sort_IWP" only contain the global field and the stored calculation part. 

                    • 7. Re: Sorting portals in IWP for list
                      paolobkk

                           Hi Jan, Thanks for the fast reply.

                           Well, yes I think we had some misunderstanding here. Let me give you a different explanation. I've made 2 fields.

                      gfield_for_sort_IWP is the global field.

                      field_for_sort_IWP is not a global field but a calculation field that I use to sort the portal , or table (may confuse the similar names).

                           The calculation in this field was resulting in a "copy" of the content of the field that I wanted to be sorted by.

                           If I put the text "code" in gfield_for_sort_IWP (by the scripted button), then field_for_sort_IWP  was resulting in the content of the field same_table::code of the same record (check out the calculation here)

                            

                      Case ( 
                            
                      gfield_for_sort_iwp = "code" ; code;
                      gfield_for_sort_iwp = "description" ; description;
                      gfield_for_sort_iwp = "category" ; category
                      )
                            
                           But in this way every user who click the scripted button is going to modify the calculated field (with its own global field direction) and when I tried from 2 different platform I had error messages (..another user is modifying this record..)
                            
                           You are totally right about the "Replace field content" that wasn't supposed to be in loop...my bad frown
                           Anyway in this situation I can't have that calculation field in the first place.
                            
                           I've been checking about the indexing and I had some improvement.
                           I've tied a portal without any kind of sorting and it takes the almost the same time to load.
                            
                           All the fields are from one table and there are no related fields or calculated in the portal.
                           However reducing the number of fields in the portal row is giving an improvement, so I'm considering to group some fields within only one stored calculation field (eg. "description & description_another_language").
                            
                           LAST EDIT:
                           I tried using stored calculation field to group the values of different fields and it seems working.
                           Every field I take out I gain 5 seconds. Now to load the portal it takes between 35-40sec.
                            
                            
                            
                      • 8. Re: Sorting portals in IWP for list
                        paolobkk

                             Just as experiment.

                             I made 1 stored calculation field to put together all the info I need in every row:

                              

                             code & " - " & 
                             description & " - " & 
                             description thai & " - " & 
                             country & " - " & 
                             class & " - " & 
                             group & " - " & 
                             category & " - " & 
                             size & " - " & 
                             price
                              
                             Placed the field as the only field in the portal.
                              
                             The loading time of the layout in IWT it's now 5-7 seconds every time I switch tabs.
                        • 9. Re: Sorting portals in IWP for list
                          jmw

                               Dude, you're welcome. smiley

                               Yip, got your sorting method now. And that puzzles me somewhat as I use the same method in a solution of mine where I don't have the 'other user is modifying' error. I just verified it and it works perfectly ok for multiple users at he same time each having differing sort orders. Does your sort script leave the record(s) open at it's end for some reason (f.e. the changing of the gfield_for_sort_iwp)? Maybe an extra 'commit records' can be of help here? I have the sort-fields in an entirely different table as where the portal-records come from, by the way. 

                               When you tested the loadtime without sorting, I assume you made sure there was no sorting done on the table through the relationship? 

                          • 10. Re: Sorting portals in IWP for list
                            jmw

                                 Just read of your experiment after I posted. 

                                 Promising result I'd say. Limiting the network-traffic is the key, either like this or perhaps by limiting the number of records in the portal. Would filtering be an option? 

                            • 11. Re: Sorting portals in IWP for list
                              paolobkk

                                   Unfortunately filtering can't be an option in this case. It's the Product list and they need to see all the records.

                                   I'll filter in other lists for sure, where I can.

                                    

                                   Anyway, by grouping data in calculation fileds I've limited the fields in the portals and it's loanind within 20 seconds, kind of acceptable.

                                    

                                   I find kind of weird this slow performance... even on iphone with 3G on FMGo it's a snap! IWP just make it slow.