14 Replies Latest reply on Nov 7, 2013 10:38 AM by TOMVREKALIC

    Show only check marked record in portal?

    TOMVREKALIC

      Title

      Show only check marked record in portal?

      Post

           Hey all
           Giving Filemaker a go here and have a newb question on something I can't seem to wrap my brain around.

           I have a table called "Employees" and a table called "Phones".  Phones has two fields inside named IMEI and one called IsActive (for the checkbox)

           Now, I'm trying to get a portal in Employees for Phones::IMEI to show only those marked Inactive (no checkmark).
           Relationships are in place with id's and fk's and everything works throughout the db - except this.

           I'm sure there's a calculation for this no?
           I've read many..many..many other posts sort of resembling this and tried them all but no luck.

           Your help is appreciated.

            

        • 1. Re: Show only check marked record in portal?
          philmodjunk

               In Portal Setup, there's an option for specifying a portal filter expression. If your IsActive check box enters a 1 into the field when the check box is selected, then your portal filter expression can be as simple as:

               YourPortalTableOccurrenceNameHere::IsActive

          • 2. Re: Show only check marked record in portal?
            TOMVREKALIC

                 Thanks for the advice.

                 My check box does in fact activate 1 in the table field formatted as Number.
                 As a filter I have Phones::isActive

                 I don't see anything in the portal unless I follow through with a control style and value list to pull from the IMEI table - but then there's no filtering I see them all.

                 I think I'm missing something obviously. lol

                  

            • 3. Re: Show only check marked record in portal?
              philmodjunk

                   What I see in your screen shot is a table view and not a portal.

              • 4. Re: Show only check marked record in portal?
                TOMVREKALIC

                     Oops.

                     Here's what I have set up as the portal and it's filter.

                      

                      

                • 5. Re: Show only check marked record in portal?
                  philmodjunk

                       And that should only show related records From Phones where IsActive stores a number other than 0 or null. This assumes that IsActive is a field of type number. Not sure that it will evaluate correctly if it is of type text.

                  • 6. Re: Show only check marked record in portal?
                    TOMVREKALIC

                         Yes it's set up for type number.

                         When activating the check mark - it's set to store the value of "1" like you see in my first screenshot with the table.
                         Just doesn't want to seem to work no matter what I try.

                          

                    • 7. Re: Show only check marked record in portal?
                      philmodjunk

                           Since you can't select this value directly in the portal given this layout design, please describe exactly how you are setting the value in the field to select an active phone record.

                           Also, make sure that no other value was already present in the field before you applied the check box format. If, for example, there was a 2 in the field and you then selected the single value checkbox that you are using now, the 2 remains in field but not visible and this will affect how the field evaluates in the portal filter.

                           And if only one record in Phones can ever be selected as "Active" for a given parent record, there's a better way that does not use a field in the Phones table, but rather uses a field in the parent table for indentifying the "Active" phone record.

                      • 8. Re: Show only check marked record in portal?
                        TOMVREKALIC

                             In my table for phones, I have isActive set up for number.
                             I have a custom value list set up for the check mark. It basically consists of a 1
                             I'm am assuming checking the box activates the 1?

                             As a brief run down - There are numerous phones in total and each have their own IMEI (Blackberry).
                             When adding the phones to the db - the user has the option to enable the phone for Employee assignment or not - hence the "isActive" check mark. The remainder stay inactive (unchecked) as spares and need not be seen as available to be assigned to the employee until required.

                             My head hurts - I've been at this all afternoon. :)
                             I appreciate all the help.
                              


                              

                        • 9. Re: Show only check marked record in portal?
                          philmodjunk
                               

                                    I have a custom value list set up for the check mark. It basically consists of a 1

                               But since your portal only shows the active phone, you can't click this field in the portal row to select an inactive phone to be the active phone. How are you doing that or is this the issue that you are having?

                               Since you should only be allowing the user to select one phone as "active" for a given employee, here's an alternative method that eliminates the whole issue and also makes sure that only one phone can be selected as "active":

                               I'll assume that your current tables/relationships look like this:

                               Employees-----<Phones
                               Employees::__pkEmployeeID = Phones::_fkEmployeeID

                               That, as you have found, works great to show all Phones assigned to a given employee. But to show the active phone, make a new Tutorial: What are Table Occurrences? of Phones and link it in like this:

                               Phones|Active-----Employees-------<Phones
                               Phones|Acitve::__pkPhoneID = Employees::_fkActivePhoneID

                               Since this is a single record, you can simply put fields from Phones|Active on your Employees layout to show data from the Active Phone's record. No portal is needed. Use a portal to list all phones and this set of fields to show the active phone. You can also set up conditional formatting to identify the active phone's portal row in the portal instead. This conditional formatting can even look and function like a check box field.

                               A button in the portal's row can perform this script to select that phone as the active phone:

                               Set field [Employees::_fkActivePhoneID ; Phones::__pkPhoneID]

                               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                          • 10. Re: Show only check marked record in portal?
                            TOMVREKALIC

                                 I'll give that a go.
                                 To answer your question - the portal shows all phones,  not just the active ones.

                            • 11. Re: Show only check marked record in portal?
                              philmodjunk
                                   

                                        the portal shows all phones,  not just the active ones.

                                   That should not be the case given the portal filter that we have specified here.

                              • 12. Re: Show only check marked record in portal?
                                TOMVREKALIC

                                     I hear you - but it lists all phones.
                                     Is maybe my check mark not being recognized as the 1 - I dunno but the field is set to number and the value for the check mark is 1.

                                     Not sure if I explained myself accurately in the beginning -The db is basically set up for "equipment lending" where the person will go through - select/add an employee, then assign an available phone, a laptop and other things.

                                     In the backend, phones (in this case) will be entered manually and marked as inactive awaiting to be assigned.

                                     When the user goes through to assign a phone to the person - what should show up are the "inactive" phones that have not been assigned already.

                                     This might sound odd - but is there a way for me to use to separate tables for this? Meaning, One table "Phones|Inactive" stores all inactive phones waiting to be assigned, and when one of them is selected, it plunks that selected one in to Phones|Active removing it from inactive?

                                     That would make life a lot easier for the next portion heh.

                                      

                                      

                                      

                                • 13. Re: Show only check marked record in portal?
                                  philmodjunk

                                       Here's a demo file illustrating what I had in mind. https://dl.dropboxusercontent.com/u/78737945/FilteredPortalDemo.fmp12

                                       The portal on the left is the filtered portal and shows only records where IsActive = 1.

                                       The right hand portal shows two methods for selecting a phone record. The small check box is the actual isActive field formatted with a check box as you originally described. A script performed by the OnObjectModify trigger is set up on this field to refresh the filtered portal. So it could be that your troubles are simply due to the filtered portal not updating correctly.

                                       The larger check box isn't even a field. It's a rectangle graphic object and the letter X added as layout text. Conditional formatting makes the letter visible and invisible and the two objects are grouped and formatted as a button to select a phone by setting the _fkPhone field in the employee record. The result looks and acts like a check box field but isn't.

                                       

                                            The db is basically set up for "equipment lending" where the person will go through - select/add an employee, then assign an available phone, a laptop and other things. In the backend, phones (in this case) will be entered manually and marked as inactive awaiting to be assigned.

                                       Thanks for clarifying. I had wondered about that and had been speculating that you were actually using "phone" as a euphemism for equipment of a more sensitive nature.

                                        I strongly recommend that you not use your original method. Assigning a Phone ID to an fk field in the employee field makes more sense.

                                       

                                            This might sound odd - but is there a way for me to use to separate tables for this? Meaning, One table "Phones|Inactive" stores all inactive phones waiting to be assigned, and when one of them is selected, it plunks that selected one in to Phones|Active removing it from inactive?

                                       It's certainly possible, but I don't agree that it would make your life easier.

                                       I suggest the following relationships for a portal from which to select a phone for an employee. The portal will list all phones not assigned to another employee:

                                       Employees>----x-----<Phones|All---<Employees|assignedPhone

                                       Employees::anyField X Phones|All::anyfield (you can choose any field here in both tables and relationship still works.)

                                       Phones|All::__pkPhoneID = Employees|assignedPhone::_fkPhoneID

                                       Phones|All is a Tutorial: What are Table Occurrences? of Phones. Employees|AssignedPhone is a table occurrence of Employees.

                                       Then a portal to Phones|All can be put on your Employees layout with this portal filter:

                                       Employees::_fkPhoneID = Phones|All::__pkPhoneID Or
                                       IsEmpty ( Employees|assignedPhone::_fkPhoneID )

                                       With this setup, you can dispense with the IsActive field all together and just click a button such as I used in the demo file to select a Phone for an employee.

                                       For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                                       Note: By using an additional table, this method can be extended to support assigning multiple items of equipment such as more than one phone or a phone and a laptop or iPad...

                                  • 14. Re: Show only check marked record in portal?
                                    TOMVREKALIC

                                         Thanks for all your help.

                                         This makes total sense now that I see it.
                                         Thanks for all your help with this and for taking the time to put something together for visuals.