10 Replies Latest reply on Mar 29, 2013 1:04 PM by philmodjunk

    Tracking Status for a Reservation

    GuilhermePrudente

      Title

      Tracking Status for a Reservation

      Post

           Hi again everyone,

           I have successfully replaced all the ID fields from my "enumeration" tables to just the name of the property and altered the relationships accordingly. It really was great since I don't have to set the data type of the fields as a drop-down menu + value list to get the "look-up" effect of having a field being set with ID but displaying the name. However, I am undecided on my current case of having several status(Proposal Pending, Proposal Approved, Proposal in Revision, Manager Approved,many more etc.) when doing script checks. 

           Example:

           Table TableName(field,field)

           Approach #1

           Table Booking Status(name) = Proposal Pending,Proposal Approved,etc.

           Downside:Have to do checks with text in the scripts, which is bad because of typos and whatever else might happen.

            

           Approach #2

           Table Booking Status(ID,name) = 1,Proposal Pending

           Downside: On every layout that displays booking status, have to use drop-down menu with ID+name to display and set it correctly. The number itself does not give any information when reading the code further along(inside scripts and such).

            

           My "Approve Proposal" button does a check if Status = "Proposal Pending". If I kept the ID's, I would have Status = 1, and that would be good because I could change a "description"(Proposal Pending) to whatever later on,if need be. 

           So I'm here to ask: what is the most commonly accepted/used way in FileMaker to handle this scenario? Should I stick to value lists populated with IDs+Name from the enumeration table(BookingStatus) on this particular case, or are there other options?

        • 1. Re: Tracking Status for a Reservation
          philmodjunk
               

                    I have successfully replaced all the ID fields from my "enumeration" tables to just the name of the property and altered the relationships accordingly.

               That isn't necessarily a good idea. One fo the reasons for linking tables with an auto-entered ID number is that this value is then never permitted to change. If you have to change one of these names--say it was originally entered incorrectly, you can have all kinds of complications getting all of the related records updated to match.

               And it's possible to use a name based value list to link records by ID number with a bit of scripting.

               This doesn't mean, however that I use ID numbers as the primary field value in every table based value list. For me it's a function of the number of values in the list and whether the user needs to be able to edit the specific values used in the list. If it's a small list of values that requires no or very limited user management, then there's no real need for that ID number in the value list. But as the list in question becomes larger and it's management becomes more dynamic, there's a tipping point where the extra design work needed to enforce unique values and manage changes becomes more than using an ID number as the primary field in the value list.

          • 2. Re: Tracking Status for a Reservation
            GuilhermePrudente
                 

            But as the list in question becomes larger and it's management becomes more dynamic, there's a tipping point where the extra design work needed to enforce unique values and manage changes becomes more than using an ID number as the primary field in the value list.

            I am also tracking the dates and account names of when the status changed... all the changes in status will be controlled by scripted buttons, and they are shown/hidden by a portal. The value list for the status and most of my value lists aren't "dynamic", they're more a "type once so we can use forever" type of thing. If I do need to add another, I am making sure no script relies on a text-based check, and also for the uniqueness of the situation.

                  

                 

            And it's possible to use a name based value list to link records by ID number with a bit of scripting.

                 Could you show me how? I am trying to link account name to EmployeeID, but the catch is that my file started on a no-table screen, with buttons set-up. That would be my "main" screen. Is there a way to fetch the EmployeeID from the account name without having a table related to my "mainscreen" or a record in it? (Makes no sense having records for a screen with 2 buttons and no fields - and like I said, it's not related to any table)
                  
                 Thanks in advance! :) 
            • 3. Re: Tracking Status for a Reservation
              philmodjunk

                   If there are no records in the main screen table, what will you do with your employee ID value once you have retreived it from the employee table?

                   A global field can be used to access related data in another table if the global field is the match field. This can be made to work without having a record in the table where the global field is defined, I believe.

                   Here's a demo file that uses a name based value list that auto-completes to link a record by ID number:

                   FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                   Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                    

              • 4. Re: Tracking Status for a Reservation
                GuilhermePrudente
                     

                If there are no records in the main screen table, what will you do with your employee ID value once you have retreived it from the employee table?

                I need it to relate the FileMaker account to an actual employee record, which is part of the "system". I can't see a more integrated/direct way and I can't take full advantages out of FMP accounts with my current knowledge. Basically: Even though we have account/privileges and such, when it boils down to "what registered employee made this reservation/booking", just having the account name doesn't cut it in my opinion, I need to link it to a record with more info other than searching by names. Does that make sense or am I being too skeptic in using names(first,middle,last) as unique values for relating data to employees?

                      

                Thanks for feedback.

                • 5. Re: Tracking Status for a Reservation
                  philmodjunk

                       I wold definitely set a text field to auto-enter the account name. I'd then add a text field to the Employee table to store their Account name with Unique Values enabled as a field validation setting. Then you can link your records by these two fields to identify the responsible employee. Given the text nature of an account name, this tends to be one of my exceptions to the rule against linking main tables by name fields.

                       But I don't see what this would have to do with the design of your main screen layout.

                  • 6. Re: Tracking Status for a Reservation
                    GuilhermePrudente
                         

                    But I don't see what this would have to do with the design of your main screen layout.

                    First of all, thanks for all the feedback, I appreciate it!

                    Well, I basically want on my mainscreen to be decided which layout to go to: Administrator - Sales - Operations, depending on the employee position/level.

                         

                    I wold definitely set a text field to auto-enter the account name.

                         I get that I can link Mainscreen::Employee Acct Name, auto-enter $$accountName with Mainscreen_Employee::accountName, and then retrieve whatever I need from Employee that way for variables. But the big deal is for this to work, I have to create a record for "Mainscreen", and I don't believe it feels right or natural to have a record for a "navigation" screen!

                         I am using the trigger OnFirstWindowOpen to get the account Name. If I had a link to Employee, I could check their department/title/etc. and send them to the correct starting layout!

                          

                    • 7. Re: Tracking Status for a Reservation
                      GuilhermePrudente

                           Is it possible that I do a Perform Find with accountName, on start-up and with no records on the Mainscreen layout, with a relationship Mainscreen---->Employee?? I think I could retrieve everything that way

                      • 8. Re: Tracking Status for a Reservation
                        philmodjunk

                             Now that I know what you have in mind, let me try that in a more specific manner:

                             Presumably, each type of user: Administrator, Sales, Operstions is given a different privilege set for their account. This enables you to identify groups of users instead of just specific individuals

                             Then a button on your MainScreen layout can do this:

                             If [ Get (AccountPrivilegeSetName ) = "administrator" ]
                                 Go to Layout [//specify layout for administrators]
                             Else IF [AccountPrivilegeSetName ) = "Sales"]
                                 Go to Layout [//specify layout for sales staff]
                             and so forth....

                             The link between an employee table and your records where the account name is auto-entered would be a method of being able to display the actual name of the responsible employee on your layouts where you display data from this table--instead of the just the auto-entered account name.

                        • 9. Re: Tracking Status for a Reservation
                          GuilhermePrudente

                               Yes! I managed to do it. I have to get better used to some of the workarounds in FM, but it's fine! All I did was use $$accountName on start-up, go to layout Employee, perform a find and use its information! :-) 

                               Thanks for all the help Phil, if you want to add better practices, I would love to hear it!

                                

                               Edit:

                               Just read your new post and you are right, as always, lol. It really is more secure to do this management that way, but all I needed was to have the ID set at all times, so I could set a global variable for EmployeeID from the very login to use whenever needed.

                               You are still far more correct than me though :)

                          • 10. Re: Tracking Status for a Reservation
                            philmodjunk

                                 $$accountName works, but you can also just subsitute Get ( AccountName ) every place where you are using that variable.