8 Replies Latest reply on May 21, 2010 1:43 PM by philmodjunk

    Protect Data after lookup

    FMMark

      Title

      Protect Data after lookup

      Post

      I am using FileMaker Pro 10 on Windows XP. The database is being published to the web via instant web publishing.

       

      I am new to FileMaker and learning on the job.

       

      The database I created is meant to facilitate a two state call center to enter customer trouble tickets into a form layout. I have setup a total of three tables within the database. The first table is the form that the user enters the call information in to. I created the layout with a tab for each state on the same layout. The fields in each tab are unique to each state. The other two tables (one for each state) archives the customer information. The two tables for each state have a relationship with the main table using the customer ID as the key. I have set the fields for the customer information on the main table to lookup the store name, address, city, etc in the other tables.

       

      The problem the user is experiencing is when they enter the customer ID and hit submit, the customer information is displayed blank on the main table and when I lookup the archived table, the customer information for that customer ID is now blank. This does not happen everytime but every so often. I have formatted the two spreadsheets that contain the customer information many times and imported this information. This has not resolved the concern. I am thinking there is some sort of sharing setting that I have not enabled. I would like the lookup to just populate the corresponding fields but when the data is entered on the main table, it overwrites what is listed on the archived table with the customer information.

       

      Please help me understand what I can do to resolve this concern. Any help on this matter would be greatly appreciated.

       

      Thank you,

       

      Mark 

        • 1. Re: Protect Data after lookup
          philmodjunk

          "lookup" is a term with more than one meaning in a filemaker system. I can think of at least three different "lookup" implementations.

           

          Please describe exactly how you have set up this look up.

           

          On another note, I don't see why you need separate tables for each state. That complicates life if one of your customers moves from one state to the other. There are ways you can load all the customer data in a single table, but only display data from a given state at a time.

          • 2. Re: Protect Data after lookup
            RickWhitelaw

            If you're using the term "lookup" the way I suspect you are, then there's no need to have the main table lookup the values for address etc. The fields from the related tables could be placed directly on the main table's layout. As long as "allow creation of new records" is enabled in the "child" tables via the relationship with the main table, data entry would work this way as well. Having your main table lookup data seems to be no more than duplication of information.

             

            RW

            • 3. Re: Protect Data after lookup
              FMMark

              Phil,

               

              Thank you for your response.

               

              The lookup i am referring to is setup via File I Manage I Database I Field I Options. I check Lookup value, then click on the specify tab and "starting with table" is Table 1 (the main table where call details are entered), "Lookup from related table" is the corresponding state tables depending on the field. There are five fields that I have formatted like this for each state (Store Name, City, Address, State, Zip Code). Also, I select "Edit Layout" for the main table layout and right click on these five fields, select "Field/Control", then "Setup", and under "Display Data from" the corresponding state tables depending on the field.

               

              Table 1 has two tabs with each state the name of each tab. The tabs have the same amount of fields and layout. There are 34 total fields, 17 for each state. The difference is the field names have VT or NH in the field name so I can differentiate the two when exporting the data. The reason I have the two states separated is one state has 6-digit customer ID's and the other has 4-digit customer ID's. If that doesn't make a difference then I will combine the two.

               

              When the user enters the customer ID, the Store Name populates but the City, Address, State, and Zip Code fields are blank. Then when the user switches to the state layout the information is blank. Yet prior to the user entering the customer ID and selecting submit, the customer information was not blank.

               

              I have a relationship from table 1 with the other two states using the customer ID as the key. The two state tables do not have a relationship. The data entered from the main table updates the information for the two state tables. I don't know if that is contributing to the error, but I would like the data to only transfer from the state tables to the main table and not both ways.

               

              Please let me know if you need any additonal information.

               

              Thank you,

               

              Mark

              • 4. Re: Protect Data after lookup
                philmodjunk
                FMMark wrote:

                The lookup i am referring to is setup via File I Manage I Database I Field I Options. I check Lookup value, then click on the specify tab and "starting with table" is Table 1 (the main table where call details are entered), "Lookup from related table" is the corresponding state tables depending on the field. There are five fields that I have formatted like this for each state (Store Name, City, Address, State, Zip Code). Also, I select "Edit Layout" for the main table layout and right click on these five fields, select "Field/Control", then "Setup", and under "Display Data from" the corresponding state tables depending on the field.

                 

                OK, first problem lies here. You are actually following Rick's advice and placing fields from the related table directly on your layout. This is a completely separate thing from the looked up values settings you've specified for fields in table 1. Generally, you want one method or the other, not both. Placing the related fields on the layout displays the current data for that customer. If you edit this data in the related field, your changes appear here automatically. You can even edit this information directly by entering the fields and editing them as these are the actual fields from the related table. If you follow the above steps but stop just before you select Field/Control | Setup...., You'll see the other method which records a "snapshot" of the data in the related table at the time you selected this customer. If you later edit the customer's record, the data you have in this table will not update automatically. I suspect that this is not what you need here, but you should decide for yourself and choose one of these two methods--not try to use both.

                 

                Table 1 has two tabs with each state the name of each tab. The tabs have the same amount of fields and layout. There are 34 total fields, 17 for each state. The difference is the field names have VT or NH in the field name so I can differentiate the two when exporting the data. The reason I have the two states separated is one state has 6-digit customer ID's and the other has 4-digit customer ID's. If that doesn't make a difference then I will combine the two.

                 

                That doesn't really make a difference nor do you need separate fields for each state. You can add an 18th field to table 1 and use it to record the name of the relevant state (or its two letter postal code such as VT or NH). You can add a validation rule on your customer ID code that uses the customer's state to confirm that the right number of digits were entered. Does this ID code come from an external source? If so, I suggest an additional serial number field to use as the actual ID code used to link your customer records and keep this ID code only as an extra data field you place on your reports.

                 

                When the user enters the customer ID, the Store Name populates but the City, Address, State, and Zip Code fields are blank.

                  

                You'll need first to select which look up you want as I've previously mentioned, then you need to check the details of how you've set this up. With looked up values, you'll need to check the field options for these fields in table 1. With the actual fields from the customer table like you have, you'll need to double click the fields while in layout mode and check to see which table, the customer table or table 1 is specified. With both options you need to have the correct relationship defined so that customerID in table 1 matches customer ID in your customer table.

                 

                Then when the user switches to the state layout the information is blank. Yet prior to the user entering the customer ID and selecting submit, the customer information was not blank.

                How are you switching layouts. This appears to be a layout that shows records from one of the two customer tables. This layout will have a completely different current record and found set from your first layout and none of the actions you describe will select the matching record on this layout. You'd need a script that either performs a find or uses go to related record to pull up the appropriate customer record.

                 

                I have a relationship from table 1 with the other two states using the customer ID as the key. The two state tables do not have a relationship. The data entered from the main table updates the information for the two state tables. I don't know if that is contributing to the error, but I would like the data to only transfer from the state tables to the main table and not both ways.

                 

                In that case, you should stick with Looked up values. double click each field and select these fields from table 1 instead of the "state" tables as you have currently selected.

                 

                • 5. Re: Protect Data after lookup
                  FMMark

                  I have changed the database settings to do the following:

                   

                  I deleted the fields for Store Name, Address, City, State, and Zip for the NH tab and the VT tab. Then I used the drag tool from the edit layout screen. On the NH tab I dragged the field onto the layout and chose to display the data from the NH retailer list table. On the VT tab I dragged the field onto the layout and chose to display the data from the VT retailer list table. So now the fields for the two states are displayed on the main table layout and I made sure to uncheck the lookup value for each field. Then I went into the relationships tab and double clicked on the equal sign that connects the tables. For the relationship from the main table to the VT retailer list table I checked "Allow creation of records in this table via this relationship" under the VT retaler list side of the dialog box.  For the relationship from the main table to the NH retailer list table I checked "Allow creation of records in this table via this relationship" under the NH retaler list side of the dialog box. Everything works how it is supposed when I am in File Maker in browse mode. When I enter the retailer number and hit tab, the store information populates correctly.

                   

                  However, when I access the database via the web on the user side the following error still occurs:

                   

                  I click on add new record, I enter the retailer code and hit submit, The store information is blank

                  On ths user side there is a drop down field option to change the layout from the main table to the VT or NH retailer list tables. when I switch the layout to the NH retailer list and locate the retailer code, the store information on that table has been deleted and only the retailer code is shown. Yet prior to entering this record I checked the NH retailer list table and the retailer code that I just entered had all the store information entered.

                   

                  After trying different entries I have realized that when I create a new record and enter "0" into the retailer code, then hit submit. Nothing popluates as it should because the "0" is not on either of the retailer lists. Yet now if I click on the record and enter a true retailer code over the "0" and hit submit, the store information now populates correctly. So if I enter a "0" inot retailer code first then swtich the retailer code to a real number it works, if I just try to enter the retaielr code initially, the store information is blank and changes the store information on the retailer list table to empty fields as well. Therefore I have no idea why this is happening on the first entry and not the second one.

                   

                  Please let me know what I am doing wrong.

                   

                  Thank you,

                   

                  Mark

                  • 6. Re: Protect Data after lookup
                    philmodjunk

                    It's difficult to say without actually examining the database structure and layouts.

                    • 7. Re: Protect Data after lookup
                      FMMark

                      Is it possible to send you a copy of the file?

                      • 8. Re: Protect Data after lookup
                        philmodjunk

                        Yes, though you can also post a copy to a file share site and then post the link here---that way others besides myself can take a look. If you just want to send it to me, click my forum name and send me a private message. I'll respond back with an email.