7 Replies Latest reply on Mar 27, 2012 3:03 PM by philmodjunk

    Multiple windows of same layout with different found sets

    JohnTracy

      Title

      Multiple windows of same layout with different found sets

      Post

      I have a need of multiple windows of the same layout each with different found sets.  The "find" contains a find on FieldA OR a find on FieldB and an omit on FieldC.  The found sets in each window are unique but may contain some of the same records.

      I've done the preliminary work and testing using a global variable for the finds.  This seems to work well for the finds but it is also used in calculation fields, script triggers, and conditional formatting.  Needless to say: multiple windows are screwed.

      The easiest fix would be to have a variable local to a window.  FM local variables are too narrow in scope and global variables are too broad.  Each window of the same layout has a unique name drawn from a linked table.  This has potential for solution (with Get(WindowName)) but not easy.  Essentially, I would need to replace "$$LinkField" with the likes of: LinkedTable::LinkField whose LinkedTable::WindowName = Get(WindowName) -- I don't know how to do that and it would be a pretty awesome script in lieu of $$LinkField.

      To make matters more difficult, not all records in the current window found set will link to the same window name.  All the records where the present $$LinkField is important in a script trigger or conditional formatting are the records that don't link to the ::WindowName.  Furthermore, these are records where the "find" is based on partial field content.  IOW, The "find" is for FieldB = *$$LinkField.  When I try to use the star(*) through some calculation without a variable, FM balks.

      Any help would be appreciated.

      I'm going to proceed by trying a global variable array.  I hope I can create the array dynamically (one member at a time.)  It would sure be nice if the index (rep number) could be the WindowName.

      Take "Variables local to a window" as a feature request.

        • 1. Re: Multiple windows of same layout with different found sets
          philmodjunk

          Needless to say: multiple windows are screwed.

          I don't see why they are from your post.

          Please explain in more detail what you are tying to do. I can't determine from your post, how you are trying to use the $$linkedField variable and/or linkedTable::linkfield. What are you trying to accomplish with these many different found sets?

          It is possible to create an array dynamically.

          Set variable [$Array[3] ; 25 ]

          For example, declares the variable with at least 3 repetitions and stores 25 in the 3rd repetition.

          • 2. Re: Multiple windows of same layout with different found sets
            JohnTracy

            Subsequent to my earlier post, I have tried array variables.  It was easier work than I was expecting and seems to be working quite well.

             

            I have another problem now.  As I mentioned before, some records can exist in more than one found set.  (They can exist in two sets but not more.)  The layout has a few calculation fields that are not important to the basic record but are important to the information portrayed in the layout.  For these recorde, the calculation fields will display different values depending on the found set the record is being shown in.  The calculation field is marked as "do not store -- recalculate when needed."  This works really well when only one found set exists at a time.  However, when the same record shows in two windows of found sets, the first one seems to win.

             

            So now what?

            Can I force a layout to recalculate when it's window is the top window?  Probably not.  There don't seem to be any window triggers except "first opened" and "last closed."

            Can I create these calculation fields with two repetitions and conditionally show only the correct repetition? I may try a test of this, but I'm not sure it can be done.

            Pull the calculation fields off to a separate linked table tied to a particular <unique> found set.  This seems to be FM's preferred approach, judging by other posts, but is probably the most work for me.

            • 3. Re: Multiple windows of same layout with different found sets
              philmodjunk

              However, when the same record shows in two windows of found sets, the first one seems to win.

              Can you give an example of that? What do you mean by "win"? Do you mean that when you edit data only the fields in the active window update?

              Can you post a sample calculation and use it to explain the problem in greater detail? (Then we can try to replicate the issue to see  what work arounds may best serve.

              Off hand, a looping script that uses select window to select each open window in turn and uses Refresh Window to refresh them may be the only way to get inactive windows to refresh for you...

              • 4. Re: Multiple windows of same layout with different found sets
                JohnTracy

                I seem to be hosed on this.  A couple of comments first.  A loop script that selects a window is no good because a window can be selected by clicking on it and there is no window-activate trigger.  Refresh doesn't force a recalculation of the calculated fields.  Win? A field can only have one value, calculated or not.  When two found sets are showing the same record and a calculated field needs to be different, one found set will have the right value and the other a wrong value.

                 

                Now for an example.  A hypothetical example, but close to illustrate what I'm trying to do.

                 

                A company transfers (sells) material or receives (buys) material.  These transfers are usually from without but sometimes are intra-company transfers.  Each department of the company has it's own register to record these transfers but each transfer is recorded in the database once and only once.  (This "once and only once" is a critical requirement.)

                 

                When department AB sends material to department XY and records the transfer in the AB register, the action shows as "sent" and the material is placed in the "Sent" field.  When department XY views this same transfer in the XY register the "action" field shows as "received" and the material shows in the "received" field.  The transaction as recorded in the database shows the way the transaction was originally entered.  However the viewing of the records is done through temporary "register" fields that lay on top of the actual data fields as field filters.  This <apparent> swapping of fields is for an occasional record that shows up in the "found set". 

                 

                Everything I have tried falls back to the same stumbling block:  That a single field needs to display different data in different registers at the same time.  What I need to do is dynamically change the field that is being viewed on a record by record basis.  IOW: if I have two repetitions of these temporary calculation field, I would need the view field show rep 1 when viewed in the register that created it and rep 2 when viewed in the other register that the record is applicable to.  It is not the content of the field that is being changed, it's the field being viewed that has to change.  Other methods have boiled down to the same realization.

                 

                I hope I'm missing something simple, but I think I'm up against a fundamental limitation of FM.

                • 5. Re: Multiple windows of same layout with different found sets
                  philmodjunk

                  Each department of the company has it's own register to record these transfers

                  Sorry, but "register" isn't a FileMaker database term. What have you set up to function as a "register" for each department?

                  You have not posted a single calculation example in your last post--only made a generalized description of something that could be implemented in many different ways.

                  However the viewing of the records is done through temporary "register" fields that lay on top of the actual data fields as field filters.  This <apparent> swapping of fields is for an occasional record that shows up in the "found set".

                  How's that again? How do those "filter" fields work? How does this "swap" fields? Remember that I can't see your database. So far, I only know what you tell me about it. Can you post the actual calculation expression you want to use?

                  Everything I have tried falls back to the same stumbling block:  That a single field needs to display different data in different registers at the same time.

                  This cannot be in any database system that I have ever worked with. If you need different data in the same field, you must use either different fields or the same field, but different records to display the data. This is not a function of data displaying in windows, it's due to the fact that a given field in a given table can only store one value and storing a new value overwrites the previous value.

                  Unstored calculations, on the other hand, can update to compute values as needed and the windows current found set could control the value returned in that window--but only under very specific situations that don't seem to apply here.

                  When two found sets are showing the same record and a calculated field needs to be different, one found set will have the right value and the other a wrong value.

                  I must respectfully disagree with that statement. The fields are displaying the correct values--just not the values you expect or need. I could easily be wrong, but this sounds like you need to rethink the structure of your database.

                  • 6. Re: Multiple windows of same layout with different found sets
                    JohnTracy

                    I have explained the problem as best as I can.  I need to be able to change the field being viewed on a record by record basis on a form. Not change the information in a field.  IOW, I need a calculation field that can change the field itself like:

                    If such-and-such, be repetition[1], else repetition[2]

                    Or, likewise to link to table 2 instead of table 1.

                    The term "Register" is my term for the form that the user sees.  It is not intended to be a MF term.  

                    Let me introduce you to field filters. Go to FileMaker Pro Help and type "field filter" into the search box.  You should find the article titled Field Filters: Formatting Data Automatically.

                    Here is an example with a calculation:

                    Department ABC enters a record that contains these three fields among others and filled with data as shown:

                    Dept  Description      Action

                    ABC   5 whatitz sent   XYZ

                    A calculation filter field is laid over the top of the "Action" field that's called "RegisterAction" with the following (non-stored) calculation:

                    If ( Dept = $$ThisDept ; Action ; "From:"  & Dept )

                    Thus the "Register" form for Department ABC is displayed:

                      5 whatitz to   XYZ         The "Dept" field is not necessary to show since this in ABC's "Register."

                    And, the form for Department XYZ is displayed:

                     5 whatitz sent   From:ABC   

                    This works great as long as only one "Register" form is open at a time.  However, if both registers are open, one register wins. the field "RegisterAction" can have only one value at a time.

                    <<<<<<<<<

                    This cannot be in any database system that I have ever worked with. If you need different data in the same field, you must use either different fields or the same field, but different records to display the data. This is not a function of data displaying in windows, it's due to the fact that a given field in a given table can only store one value and storing a new value overwrites the previous value.

                    >>>>>>>>>

                    I take this comment that you agree that FileMaker is not competent for the task.

                    • 7. Re: Multiple windows of same layout with different found sets
                      philmodjunk

                      If such-and-such, be repetition[1], else repetition[2]

                      It's the "such and such" that left me unable to suggest much that can help you much as you hadn't told me what that is in previous posts.

                      Let me introduce you to field filters. Go to FileMaker Pro Help and type "field filter" into the search box.  You should find the article titled Field Filters: Formatting Data Automatically.

                      This is found in the Knowledge Base, not in Filemaker help. It's a concept that's been around awhile and isn't the only option for this type of thing. The addtional info helps. Telling me that you are putting one field on top of another without this detail didn't give me enough to go on.

                      This works great as long as only one "Register" form is open at a time.  However, if both registers are open, one register wins. the field "RegisterAction" can have only one value at a time.

                      But of course. Since both calculations refer to the content of the same global variable, you get identical results in both windows. It has nothing to do with which window is in front, it's a matter of what value was assigned to the global variable last.

                      I take this comment that you agree that FileMaker is not competent for the task.

                      Not at all. I think the approach you are trying to implement will not work but there are other approaches that can work. I could easily be wrong here as I don't know nearly enough about your database and how you need to get this to work, but I think you could set up a table of departments (one record for each department) and base your windows layout on this table, then make your "register" a portal to a related table where the transactions are documented. Now each window can be set to a different record for a different department and now you can display department specific details within the portals on each layout.