9 Replies Latest reply on Dec 7, 2011 3:15 PM by mgores

    HELP!!!! Somebody who knows what they are talking about

    AaronRoss

      Title

      HELP!!!! Somebody who knows what they are talking about

      Post

       

      Hey i am new to filemaker but i am picking up pretty quickly. I am good not great at writing scripts but i suck at making relationships between tables. i need help with a couple of things but plz know what you are talking about and talk dumb to me lol. The database i made is split up into four parts; Inside Sales,  Verifier,  Outside Sales, and Managers.

      1. I have set up 50 possible accounts for inside sales to login to. Each account is scripted to go to a particular layout that equals the account name. The accounts names are permanent and can NOT be modified since they are attached to a navigation script. I want a way to create 50 user names that can be added, changed, or deleted by managers. The idea is that a manager can creates a user name  and assigns it to one of my 50 accounts. The username wil then populate in every layout created in that particular account. If needed, the manager can change something or delete the user without ever deleting the account itself.

      ex: John Doe walks into the call center and says, "I want a job." the manager logins on his side, clicks [add user] and sets up John Doe. John Doe is then given by his manager a login and password. The login navigates to a layout that says, "welcome John Doe." in addition, every time he creates a new record, his name will automatically populate under a field named "repersentive." but then, one day John Doe tells himself he can't do it anymore and he quits. In walks Jan Doe and she says she wants a job. The manager simply logins under his account and clicks [delete user] some how he deletes John Doe and adds Jan Doe. Manager gives Jan Doe the same login and password and now her layout says, welcome Jan Doe. Keep in mind that this would have to support 50 users and web publishing.


      2. The second thing I need help on is actually an extension of the fist thing I mentioned above. I need a way for a manager with none admin privileges to be able to add these usernames delete them and change them if needed. Also, I want his layout to be able to somehow  assign the username to any of the 50 accounts without bugging me. In addition, I need a calculation that would someone stop the manager from assigning a new user to an account that's already in use by another user.


      plz note that's any solution has to work with web publishing. Also, the user names can be fields created in a table. I am not talking about the system usernames but if I can use that to assign multiple names that would be perfect. Also plz refrain from using a solution that deals with relationships. I have exhausted myself in trying to set up relationships with other tables. I simple just do not get it. lastly the users added and deleted would have to connect Somehow to a value list that would show up in other parts of the database. The idea is that when a manager adds a new user that info will show up in a drop down menu in another department using a different table.   


        • 1. Re: HELP!!!! Somebody who knows what they are talking about
          revmk

          Hello Aaron,

          I'm struggling to understand why you are needing to script layouts to user names.  If it is just to populate usernames into fields you could just 'call' them into the field using a set field option and specifiying the currently logged in user (assuming you are using the Filemaker user account setup).  You can also call this field on layouts to say hello, goodbye whatever you want really.  I see no reason why you couldn't return all users listed in the filemaker user control if required too.

          In respect of your second point(s).  I'm concerned that by limiting your the number of users and relying so heavily on scripted layouts you will end up having to 'fix' various problems.  This is going to be especially true if you allow 'non-admins' to carry out admin tasks.

          Relationships in Filemaker are very easy - once you understand the concepts behind them.  Is there something in particular that is causing you problems in understanding them?

          If you post an example of your system - with dummy data I'm sure we can take a look for you.
          Thanks

          • 2. Re: HELP!!!! Somebody who knows what they are talking about
            mgores

            Aaron, one of the keys to setting up relationships is to use ID fields.  Typically you want one one of these fields in each table.  You set these fields up to be auto entered serial numbers with unique values.  Then when you want to relate one table to the other you include a foriegn key (the ID from the first table) as a number field (not auto entered) in the second table.  So you would have something like this

            Table 1           Table 2

            Table1ID ----< fkTable1ID

                                   Table2ID

             

            Now a record in Table2 (say a sale)  has the same value in fkTable1ID as a record in Table 1 (salseman) has in  Table1ID, it is related.  That way you can link a salesman to a sale, or even multiple sales.

            • 3. Re: HELP!!!! Somebody who knows what they are talking about
              philmodjunk

              Also plz refrain from using a solution that deals with relationships. I have exhausted myself in trying to set up relationships with other tables. I simple just do not get it.

              Filemaker is be design a Relational database. Without using relationships, you will be severly limited in what you can do with this product. It's kind of like asking a baker for a fresh loaf of bread but telling him he can't use an oven to bake it.

              Feel free to ask questions as we hit areas you don't understand how/why what we suggest works, but we will need to include appropriate relationships to set up what you are requesting.

              If you haven't done so already, perhaps doing this tutorial on table occurrences may help you better understand how relationships work: Tutorial: What are Table Occurrences?

              You spelled out a pretty good list of things you want. I suggest you break this down into sub tasks and tackle one at a time until you get it all up and running to your satisfaction.

              RevMK and Mark have made very good points about your request for layouts for each account. This approach greatly limits what you can do with FileMaker. Usually, we can set up a single layout for this purpose that changes its appearance (To display "welcome John Doe" for example) and the records that are accessible to fit the preferences/specifications for that specific user. This makes adding/removing users much easier to do. So let's start by answering the question already asked: "Why do you need the separate layouts for each account?"

              Describe what would be different on each such account and we can then advise you on how, if possible, you can do this with one layout.

              (And adding/removing users can be done, with scripting, by a user that does not have full access permissions for the database.)

              • 4. Re: HELP!!!! Somebody who knows what they are talking about
                AaronRoss

                 

                Ok, first let me apologize. I have a hard to getting my point across so I will go into details.

                Like I mentioned above, the database is split into four parts: inside sides, verification, outside sales, managers (and maybe a vault that contains red data)

                INSIDE SALES: Privilege Set; Telemarketers [data entry]

                Job: Set appointments for outside sales

                Number of Account Names: 30 (in theory each account name would be assigned to a Homepage. Remember that these account names will always be changing due to employment status)

                Homepage Layout: "welcome <<account name>>!" (In theory from here account user would go to his/her homepage to choose to set or view leads, appointments, or callbacks)

                Layouts: User01::LEADS, User01::APPTS, User01::CALLBACKS (in theory, each layout is connected to a separate table)

                Tables:  LEADS, APPTS, CALLBACKS (in theory, each table is kept separate from other users. this way User01 cannot view records from User02 ect ect)

                 

                VERIFICATION: Privilege Set; Verifier [data entry]

                Job: Verify appts set by telemarketers

                Number of Account Names: 2 (in theory each account name would be assigned to a Homepage. Remember that these account names will always be changing due to employment status)

                Homepage Layout: "welcome <<account name>>!" (In theory from here account user would go to his/her homepage to choose to verify appts, presets, or kick appts back to the user)

                Layouts: Verifier01::Unverified APPTS, Verifier01::Verified APPTS, Verifier::Kickbacks (in theory, each layout is connected to a separate table)

                Tables:  Unverified APPTS, Verified APPTS, Kickbacks, and Presets (in theory, each table is kept separate from other users. this way Verifier01 cannot view records from Verifier02)

                OUTSIDE SALES: Privilege Set; Outside Sales [View only or only able to modify one field]

                Job: Run appointments

                Number of Account Names: 15-20 (in theory each account name would be assigned to a Homepage. Remember that these account names will always be changing due to employment status)

                Homepage Layout: "welcome <<account name>>!" (In theory from here account user would go to his/her homepage to choose to view appointments)

                Layouts: User01::View Appointments (in theory, each layout is connected to a separate table)

                Tables:  Appointment results (in theory, each table is kept separate from other users. this way User01 cannot view records from User02 ect ect)

                 

                Manager: Privilege Set; Admin [Not really full access admin but enough privileges where they won’t bug me]

                Job: Oversee verified appts and assigned them to outside reps

                Number of Account Names: 1-5 (in theory each account name would be assigned to a Homepage. Remember that these account names will always be changing due to employment status)

                Homepage Layout: "welcome <<account name>>!" (In theory from here account user would go to his/her homepage to choose to view verified appts, unverified appts and kickbacks and assigned them to outside sales)

                Layouts: Manager01::Unverified, Manager::Verified, Manager::Kickbacks, Manager:: User Settings (in theory, each layout is connected to a separate table)

                Tables:  Verified, Unverified, Kickbacks, Add/Delete Users 

                 

                Ok I hope you now understand the concept of what I am trying to do. I have most of this done already, i.e.: layout designs, tables, account names, 70% of script writing, but there are a few things I need help on.

                1. I need a general way to import a record from another table in the same database. EX: When a telemarketer sets an appointment, there is a button called [set appt] when pressed it would take that current record and all data in it to verification ::unverified appts and import it there. The way that I’ve done it is a strenuous [go to layout, go to field, copy field, go to other layout, go to field past field ect ect] I want a script that I can apply to every telemarketer's appt layout without re-scripting it over and over for each of the 30 accounts. I think this is where a relationship would be perfect but I have tried but I don’t get it. I’ve tried to go to relationships in the database and create a relationship between User01 APPTS:: account id (switch is a serial number) = Verifier01:: account id, but when I go to Verifier01 and relate the fields to User01 (You know, get the yellow light to show up instead of the green light) still nothing shows up. I tried the opposite way, still nothing showed up. I even bought Lynda: Essential Training but still I guess I am missing something.

                2. Most importantly, Because Telemarketers come and go I scripted a way for managers to add and delete account names. The way I did this is I created a User list for managers with 30 records. Each record represents one possible account name for a rep to login to. Each account name is assigned to (in theory) a user homepage for that user to set leads, appointments, and callbacks. The thing is 1st I need a login script that based on the account name will go to the assigned homepage. Keep in mind 2 things, I’m dealing with massive account names and the account names over time will be added, changed, and deleted. It’s not as easy as:

                If [get account name] = "John Doe"

                [Go to layout::User01]

                Because John Doe might not be there tomorrow. Is there some type of way that a script can run after account has been logged into that will maybe go to the managers list of users, run a search, populate something and maybe like whatever comes up can be linked to a layout of my choice?

                • 5. Re: HELP!!!! Somebody who knows what they are talking about
                  mgores

                  I understand that you have a lot of work in those layouts, scripts, etc.  and a reluctant to redesign the whole thing.  I've been there and only recently learned the advantages to the methods we've been suggesting to you so far.  But they will make for a much easier to design and more stable database in the long run.

                  For example if you have one table for appointments that is linked to the user table by userID.  You can limit the users to accessing that table through a portal that will only let them see thier appointments.  A manager or someone doing the verification can access all of the appointments to see who has what scheduled and flag it as verified or not.  You would only have one User layout that populates based on that user's ID.  Adding new employees would only require entering that employee's info into a new record to create a new ID, where you can set up the new user name and password.  When an employee leaves, a manager can flag his record as "no longer here" and have it prevent anymore entries from that userID.

                  • 6. Re: HELP!!!! Somebody who knows what they are talking about
                    AaronRoss

                     By the way, if there is another way i can do this i am open ears. I saw what RevMK posted. So how can i use ONE homepage for the telemarketers that would navagate to leads, appts, and callbacks? Keep in mind that records from account name: John Doe has to be seperate and no way that account name: Sam Doe can view or access them. Would i be using muliple tables or one? I preferr mulitiple tables.

                    • 7. Re: HELP!!!! Somebody who knows what they are talking about
                      mgores

                      If you limit the user to the one layout where they are accessing the leads, appts, callbacks through a portal they will only see those records that were created with thier userIDs.  So there is no way that Sam could look at Johns leads.  You do have the option of giving managers access to a different layout that would let them see the leads from all of thier employees.  So that Bill, who is the manager over Sam and John could see both of their data, but not see the data from Robert's employees.

                      • 8. Re: HELP!!!! Somebody who knows what they are talking about
                        AaronRoss

                         see, now u lost me. I kinda know what you are talking about but i also know it deals with relationships. So if i created a table for appts, what is the process of setting up a relationship with userID. plz use the "for dummies" version lol

                        • 9. Re: HELP!!!! Somebody who knows what they are talking about
                          mgores

                          Ok.....So I will assume you have a user table that has a field called userID which is an autoentered serial number and a unique value.  The user table will have all of the other field you want to identify an employee with like first name, last name, managerID, start date, status, etc.

                          Then you have an Appt table, that has a field called apptID, autoentered unique serial number.  Then a field called userID (number field) and a managerID (number field).  Plus all the fields you want for the appointments like customer name, location, date, time.

                           

                          Now in the manage database/relationship tab you will see these two tables.  You want to draw a line between them connectiing the userID fields in each, this establishes the relationship between the users and their appts.  Once you've done that there will be a little box with an = sign in it.  Doulbe click that and a dialog box will come up.  In it you will see the two tables listing their fields, the relationship you just established Users::userID=Appt::userID and some options for each table below that.  In those options select the "allow creation of records via this relationship" box on the Appt side.  This will allow the creation of appts from the user's layout.

                           

                          On the user layout based on the user table, insert a portal - define the portal to use the relationship you just created and select the fields from appt that you want the user to be able to enter.  Note you do not have to and probably shouldn't include the ID fields in the portal.  You can also select the number of rows to display.  Once you click OK the portal will show up on your layout and you can resize and position the portal and fields that are in it to look the way you want.

                          Once this is complete save the layout and go back to browse mode.  You will see the portal there with a blank row.  you can enter data into the fields on that blank row and as you do, you will see another blank row created below that.  The appt record you created there will contain the userID from what ever user's record you created it from.  If you go to another user's record, their portal will not show the appt created by the first user, because the portal will only show appts where Appt::userID matches the User::userID