1 2 Previous Next 26 Replies Latest reply on Jun 8, 2011 3:51 PM by philmodjunk

    Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...

    RobertWinter

      Title

      Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a Specific Field Value from that Table

      Post

      Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a Specific Field Value from that Table.

      As only a potential point of reference for those familiar with I am heavily editing FMStartingPoint2's Solution as a foundation for building my solution.

      I am new at this. I have several Tables, but two basic ones Accounts and Contacts. I want to be able to have three different layout views (with each layout having a table for detail, list or table view) of Accounts (each layout is slightly different, on the order of 10-20% different). The key question for me is that each layout will be a subset of the records from Accounts. Specifically, a 'type' field in Accounts (value = Institution, Advisor or Agent) will be used identify the selected/subset/filtered records from the Accounts Table. And upon entering the data entry mode of each layout (which will have the corresponding names of Institution, Advisor and Agent) must never be able to access the records outside of their 'type'.

      One thought and perhaps a good one or bad one is to use ... OnLayoutEnter and then Perform a Find based on the 'type' value as discussed above (though there maybe 10,000 in the database, so do not know how efficient this is...plus after selecting I am going to one to do a sort on the selected records based on two other fields in the table). Second idea, perhaps some sort of table occurance relationship needs to be created, but have no idea how to proceed this way.

      Whatever the solution, if you could clearly communicate an efficient, clean solution that would be most appreciated (i.e., assume I do not know much other than the basics plus a little more).

        • 1. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
          philmodjunk

          Can you make this work with a large portal on your layout?

          That may be simplest for a "newbie" to set up.

          Use Manage | Database | Tables to create a new table with just one field defined in it. (You may already have a table that you can use for this defined in your system.)

          Click the relationships tab and create this relationship by dragging from a field in your Accounts table to the field in this new table:

          NewTable::anyField x Accounts::AnyField

          To change the operator from = to x in this relationship, double click the line linking these two and use the drop down in the dialog that pop's up.

          Select "NewTable" (or whatever you name it) in Layout Setup | Show Records From for each of your three layouts.

          Use the portal tool to add a portal to Accounts to each layout. In portal setup, click the Portal Filter option and enter an expression that will filter out all but one account type.

          Accounts::AccountType = "Institution"

          Would filter your portal to just account records of type "institution".

          If portals won't work for you here, things get a lot more complex...

          Do you have FileMaker Advanced?

          Apologies in advance, but this isn't a trivial undertaking.

          On LayoutEnter can be used to either Perform a find for all records of a specified type or it can constrain your current found set to a specified type--which omits all records from the current found set not of that type.

          But that's just the first hurdle. If you perform your own find on this layout, you can easily pull up the other records. Show All Records and Show Omitted Only can also bring them up. With FileMaker advanced, you can use a custom menu to either remove some of thes optiosn from the records menu or replace them with scripts that appear to do the desired action, but also enforce the "only one type" restriction.

          You can use an On Mode Enter [Browse] Script trigger to perform a script to constrain the found set so that any find performed by the user automatically filters out records of the wrong type.

          You can also use Manage | Security and a "lock" expression such as Get ( LayoutName ) = Table::AccountType to restrict access to only the specified type of account.

          See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a detailed description of how to set this up. This could greatly simplify the need to use triggers and custom menus to limit records on each layout.

          • 2. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
            RobertWinter

            PhilModJunk,

            Thanks much. I do have Filemaker Advanced. And I appreciate your ideas, though all sound like kludges. Perhaps it would be simpler to setup three different tables that are pretty much the same (instead of just accounts then...tables with the names of institutions, advisors, agents) and go that way. What do you think? Is that the cleanest and most straightforward. (And perhaps I can create copy say from agents to institution if need be).

            Other ideas?

            • 3. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
              RobertWinter

              Here is another thought. What if I choose to have only one layout with a field = Institution, Advisor or Agent. And based on that value have certain fields grayed-out or un-modifiable. So if = Institution then a tabbed portal is grayed-out (how?) or data can not be entered. If Advisor or Agent then tabbed portal works but several fields in the main body are grayed out or un-modifiable. Prefer grayed-out and un-modifiable if possible. 

              How do you do? Again thanks much

              • 4. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
                philmodjunk

                Robert Winter, Kludge is in the eye of the developer. We have to use what works. If you want to call it a kludge, call it that, but it still get's the job done.

                I can't really make any suggestions on alternative approaches without getting an answer to this question: Why do you need different layouts for different sets of records from the same table?

                Splitting your data into separate tables is not something to do without analyzing the pros and cons very carefully. It may solve your immediate issues, but also may create other more difficult problems for you.

                • 5. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
                  RobertWinter

                  PhilModJunk,

                  I do appreciate your help. Just a little frustrating trying to do something that seems should be straightforward (ie other folks are doing). I have FileMaker Pro 11 Advanced and once solution is developed I will distribute in a runtime mode (or perhaps via IWP) to my other partners.

                  Maybe describe a little more of what I am trying to do. We are an venture capital fund that is in fundraising mode. As such we want to track potential investors (Institutions, could be hundreds/thousands - including things like their amount under mgmt and interest in currently investing -- ie unique to this constituency/type).

                  Advisors and agents (dozens) are firms that might either support/represent a number of Institutions (do not want to show amount under mgmt, etc. as has no value, but do want to show list of the list Institutions they are supporting/representing and note information on each tied to the last note in the actual record on the Institution). I want to be able to add Institution to and Agent/Advisor "layout" and they could all be the same layout just "grayed out" areas.

                  So yes that is why original thinking same table (call it FIRMS) and CONTACT table to go with it (multiple contacts per firm).

                  So another way might be conditional file access based on the value in the field on one layout. If Institution (say default value upon entering layout) then certain fields can be modified (like amount under mgmt, etc.) and portal tab that list Institutions (for advisors/agents) is "grayed-out. And if Advisor or Agent is entered (say via a dropdown) then items like amount under mgmt is grayed-out ant portal tab listing Institutions/Investors is available to enter investors (that will populated as Institutions or go that view to enter more info). Not really all that complex and hope this is clearer (maybe not).

                  I dont care on exact method (one or multiple layouts for FIRMS, though the more I think about multiple Tables for each constituency/type maybe a mess, though an field access I want to tie to entire solution...dont want to have to think about each user in future and set up that sounds cumbersome), just want it to be as straightforward as possible so someone else in the future can easily edit or change as need be.

                  • 6. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
                    philmodjunk

                    Keep in mind that I have no idea what tables and relationships you've currently designed here. That leaves me at a major disadvantage for making any suggestions. The ones I posted earlier are pretty straight forward, wouldn't see them as Kludges at all--but don't care to debate word definitions as what matters is what works--not what we call it.

                    This is the key question: Is the ONLY difference between your layouts, the set of records that are to be accessed from it?

                    If so, then you don't need multiple layouts, this can be done with a single layout. Even if there are a few small differences, you can still likely set this up with a single layout, but add design elements that "customize" it to the specified set of records being pulled up for a given user.

                    • 7. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
                      RobertWinter

                      Yes the only difference would be the fields that can be accessed (attempted to share in my previous post but I may not have made it clear). So sounds like layout will work.

                      How do I limited what fields (ie a few small differences). Could you elaborate on what you mean "add design elements that customize" in the context of what I am trying to do. Again thanks much.

                      • 8. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
                        philmodjunk

                        Don't know enough about that "10-20% difference" to be very specific here.

                        Speaking just in general terms:

                        Conditional formatting, invisible tab controls, fields from a related table and portals can all be used to hide/reveal information selectively. In some cases, you can just leave fields on the layout that aren't required by 100% of your users, but use access controls in Manage | Security or a validation rule in Manage | Database | Fields to prohibit changes to the field. (Script triggers can also control field access.)

                        Much depends on the details of how this "field by field access" differs for different sets of records and users.

                        • 9. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
                          RobertWinter

                          PhilModJunk,

                          Ok. I have been playing around (sure wish there were some :best practices and examples" for various usages of FileMaker. That being not the case here is what I have learned and am thinking.

                          I have only one layout. On that layout I have a field which can be one of three values (like discussed above). I have learn based on the value how to say gray certain labels/fields thru conditional formatting of. I am not sure if "graying" a tab control tab makes sense (your thoughts, obviously easy to do), you mentioned invisible tab controls (I am not mention what you mean outside of the tab control function accessible by double clicking on the tab while in layout mode...perhaps you can clarify if that is something for me to be aware of).

                          Now I know this will not stop access to the fields or change the fact it will tab through them when they are grayed out (again based upon one the three values as discussed above). So how do I lockout the editing, etc. and tabbing (if this is possible) of those grayed out fields. Do I use Records Access Privileges?, which sounds possible but given I will gray out 6-8 fields and a checkbox set seems a little more involved. But if that is the best then please walk me thru, I have played with the Manage | Security | Privileged Set and read the help...its still a little vague. Or do I use some validation rule and script triggers?, perhaps less sure how to do this, but examples for you may help on both. Or just pick one and I will go with it.

                          Bottom-line, I want to use the value of a field on a layout to gray out and limit access to a number of fields, including at least one tabbed element/object/whatever you would call it.

                          Thanks again...I will get this eventually, appreciate your help...then move on. cheers, Robert

                          • 10. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
                            aammondd

                            You can use an on object enter script trigger to immediately move the user through the "greyed out" field.

                            You would have to probably set a global field with the userid when they enter the layout

                            then use a relationship to that global field to evaluate if "editing" is allowed if not go to field next. (or a more complicated calculation to derive the next field)

                             

                            • 11. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
                              philmodjunk

                              If you use script triggers to control access to the field, it's a good "safety net" to add a validation rule that rejects edits to the field if another field in the record indicates that the field should not be editible. That helps ensure that any "gaps" in the layout design still can't result in an unauthorized edit of the data.

                              You can also use Record Level Access Control, but it takes a design change to your tables. The way this works is that you split your tables into two parts. Table 1 has all fields that are always accessible. Table 2 has all fields that are conditionally accessible. You link the two tables by a serial number field defined in Table 1 with "allow creations of records..." enabled for Table 2 in this relationship. You can now place all the fields from both tables on the same layout, but when you use access privileges to block access to the fields in table 2 (which can be done on a record by record basis) the fields from Table 2 on this layout become inaccessible.

                              See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a detailed description of how to set up record level access controls.

                              "invisible tab controls" are tab controls with fill and border settings that make them invisible. You put the items you want to appear/disappear on one tab and leave the other tab blank. Give each tab an object name and then a script that uses Go To Object to select one tab will make the objects appear. If it selects the other tab, they disappear.  This is not practical on a list view layout, but can work well on a form view layout.

                              Here's a link to a discussion of different "visiblity tricks" you can experiment with: move an object in browse mode

                              • 12. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
                                RobertWinter

                                Thanks much. I think I can make work.

                                Late thought (I am getting near the end), would it not be more straightforward to (and I know it sounds like I am going backward, but bare with me, I know a little, albeit a little more) ...

                                ...to have the three different layouts (as mentioned earlier ... say Institution, Advisor, Agent ... and with only the fields on it that apply to that layout) and one table (all the information) and then based on going to the layout have a script upon entering which among other things sets the value of a field to one of the three values that can not be modified in that layout (again mentioned earlier ... Institution, Advisor, Agent).

                                That way you have somewhat hardcoded each of three layouts (which each would be somewhat unique looking), i.e., fields on one layout that are missing would never be filled in since those fields would not show up (I know it does not lock the those fields, but if you cant access by not seeing them is that not ok on the integrity of them). Example: on Agent layout you would not have the fields, say, AUM, Amount to Investment, Timeframe, on the layout ... so for records where the firm type is Agent those fields in the table would just be blank (if you looked at what the actual record contained, say in a spreadsheet export).

                                • 13. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
                                  RobertWinter

                                  And if so what might the script element look like ... ie how to set a Field to a specific value (Get(ActiveFieldContents) ????)

                                  Upon going to Institution Layout script would include:

                                  GET(Firm_Type) - Institution   ???

                                  Upon going to Advisor Layout script would include:

                                  GET(Firm_Type) - Advisor    ???

                                  • 14. Re: Multiple Different Layout Views each with Only a Select Set of Records from One Table based on a...
                                    philmodjunk

                                    It's an option worth considering. The down side is that you now have three layouts to maintain instead of 1.

                                    To set a value in a field you only need the set field instruction:

                                    Set Field [YourTable::YourField ; "Institution" ] would enter Institution into the specified field. Do this while in find mode and you can use it to perform a find for all records with "instiution" in that field.

                                    1 2 Previous Next