9 Replies Latest reply on Jan 19, 2010 1:04 PM by ninja

    Populating Fields

    johnRees

      Title

      Populating Fields

      Post

      I am using filemaker 10 and trying to populate a foreign key. I have the relationships set up between the customers and the invoices such that the kf customers in the invoice field is populated by the kp from the customers. I have ensured that the kp is set to be read as the customer name and chosen the choose second field option so this can happen.  When I click into the field the drop down menu occurs and lists all the names of the customers as expected. However, when I click on the chosen customer from the menu two things occur. First the customer kp code is entered and in addition there is a message that states that the kp field 'is defined to contain unique values only. Allow this duplicate value.

       

      Can anyone help.

       

      Kind regards

       

      JOhn 

        • 1. Re: Populating Fields
          ninja
            

          Howdy John,

           

          It sounds like you have a validation set on your kf in Invoices to be unique.  Unless you only plan on invoicing each customer once, this is probably unwise.

           

          In File>Manage>Dbases, find the field and double click on it.  Look at the validation tab and see if you are requiring the value to be unique.

           

          You probably (read definitely) want the customer side of the relationship (pk) to be unique, but you then have a one to many relationship...the many side can't be unique too...there are many.

           

          If this is not what the problem is, please post back.

          • 2. Re: Populating Fields
            johnRees
              

            Hi Ninja,

             

            Now having amended the unique value the drop down menu occurs but when I click on one of the customers in the list the message states "This action cannot be performed because this field is not modifiable?

             

             

            • 3. Re: Populating Fields
              ninja
                

              Howdy john,

               

              That's a common error when you go to type data into a field that is set up to be a calculation...

               

              The way you describe your value list looks good...so lets talk about the field itself.  What kind of field is it?  As a key field, it should optimally be text or number.  Check on File>Manage>Dbase and look at the field Invoice::kf.  What can you tell me about that field?  Be thorough...Type, autoentries, validation, storage...

               

              The Field type shouldn't be calculation...is it?  If so, change it to "text" (bottom right of the 'Fields' tab) and see if that doesn't get you the rest of the way there.

               

              Also check: do you have any autoenter functions going on?  do you have any other validations going on?

              • 4. Re: Populating Fields
                johnRees
                  

                Hi Ninja,

                The field is set up as a text field. In total I have an invoices layout displaying records from the invoices table. I have a kf field which is a text field which has nothing checked in the auto enter. In validation I have the "only during data entry highlighted. The kf field is populated with the kp field from my customer table as are the names and addresses. The kf field is a drop down field where the values are displayed from the customer value list. In this box we are displaying data from the customer address list and the kp is highlighted from the customer list. In the specify fields for the value list the kp addresses is highlighted from the customer table and the customer name in the second field box also from the customer table. The show values from the second field is also highlighted. All is well and the drop down menu shows the list of names but will not enter due to the 'this field is not modifiable' message.

                 

                John 

                • 5. Re: Populating Fields
                  ninja
                    

                  Hi John,

                   

                  Let's check out something more basic...the problem seems to be with the field itself.

                   

                  Disconnect the dropdown from that field and set it to be an edit box instead (for now).

                  make sure the field is enterable in Browse mode.

                  Now go to that Invoice layout and try to type something into that field.

                  hit "Enter" or click outside of the field when done.

                   

                  Do you get the same error message?  A different Message?  No Message?

                   

                  If you get a message, delete the field from the layout, put a new field back on (delete and replace the same field) and try with that one.

                   

                  What we're trying to do is verify that the field does indeed let you put ANY data into it.

                  • 6. Re: Populating Fields
                    johnRees
                      

                    Hi Ninja,

                    Many thanks for your consideration on this matter. I have now solved the problem. In part it was due to the fact that I unwittingly was using a calculation field linked to the kf/kp fields.  In addition, I think I had an error in my table relationships.  I cannot be exactly sure the extent of the issue as I went back to basics and started again; and fortunately it now works.

                     

                    Whilst on the line I am also trying to link the date that a merged letter is sent/written to a field in one of my layouts that relates to contact information.  I presume that I will need a script to enter the 'current date' into the layout field.  Do you have any thoughts please.

                     

                    Kind regards

                     

                    John 

                    • 7. Re: Populating Fields
                      ninja
                        

                      Glad to hear that it works, John.

                      As I mentioned in my first post, that is a common "oops" stemming from trying to put data into a calc field...I sorta thought it was that.

                       

                      Yes, a script would do what you want, but let's make sure you do it the easiest way:

                      Could you describe the step-by-step of how you are sending/merging the letter?  If you are doing the letter outside of Filemaker, that's OK...just say so and we'll set the date separately from the letter sending event.

                      • 8. Re: Populating Fields
                        johnRees
                          

                        Hi Ninja,

                        The letter is created within filemaker. The date on the letter is 'created' by using //. In principle, I have set up some standard letters which when they are printed I want the date on the letter to enter into a field in my contact list. Because of my lack of experience I do not see how I can make a connection with the // symbol that will become an actual date in the contact field on another layout to show the date the letter was sent.

                         

                        Thanks for your help

                        John 

                        • 9. Re: Populating Fields
                          ninja
                            

                          So your step-by-step is:

                           

                          Go to the letter layout

                          Hit the print icon in the toolbar

                          ?

                           

                          Or something else?

                           Please understand that I'm trying to tie the date entry to another action you are performing so it isn't a separate task you have to remember to do...But I need to know what the steps you are doing are to pick the appropriate one to tie it to.

                           

                          As a discrete function, you would create a field called "DatePrinted" (format as Date or Timestamp) and then write a simple script with one line:

                           

                          Setfield [DatePrinted ; Get(CurrentDate)]  ##or Get(CurrentTimeStamp) if you went that route ##

                           

                          This would be easy to tag onto a printing script... if you already run a printing script.

                          If not, you would have to launch the script yourself whether by dropdown menu or tagging the script to a button on your layout...or add a second line onto the script and MAKE it a printing script:

                           

                          Setfield [DatePrinted ; Get(CurrentDate)]  ##or Get(CurrentTimeStamp) if you went that route ##

                          Print []