7 Replies Latest reply on Jan 20, 2010 3:31 PM by philmodjunk

    Auto create records + D.down lists

    Elie

      Title

      Auto create records + D.down lists

      Post

      Hello, I'm working on a technical support database using filemaker pro 10, and would need some help.

      1- I would like to know how to auto create records in a table when an action is made in another related table, what i mean is: I need to create records in a table called Maintenance Dates that contains a field called Client Id, and another one called schedueled date that will contain a non modifiable value based on a condition (maintenance terms) in another table.

       

      2- I need your help to know if it is doable to make a drop down list that contain only related values to another field in the same layout,

      what i meanis that after entering a the client's name i would like the field "client's contacts" to contain in the drop down list only the related values to the client's name entered before, the values would be exported from a previously made table that contains records showing the client's contacts.

       

        • 1. Re: Auto create records + D.down lists
          philmodjunk
            

          Do you know about Portals? It's very possible that you can set up exactly what you need here with a portal and "Allow creation of records via this relationship" enabled for the Portal's table occurrence. If you can't quite get that to work, try posting an exact example of what you want to do here.

           

          "...the values would be exported from a previously made table that contains records showing the client's contacts"

          Why exported and why exported into a field instead of a table?

           

          A related table of contacts would be very handy here for setting up a conditional value list to do what you want.

           

          Here's a thread on setting up conditional value lists:

          Custom Value List?

          • 2. Re: Auto create records + D.down lists
            Elie
              

            thanks for ur reply,

            actually it was useful concerning value lists, 

            about the first issue i actually quite know about portals, but it isn't what i need, i want to auto create records in a table called "maintenance dates" when a user enter a maintenance term in the "Maintenance table", in eg if the user clicks on "yearly", 12 new records will be automaticly created in the "maintenance dates" table containing the "client's id" and serial monthly dates (eg: 1/1/2010 , 2/1/2010  , 3/1/2010 ... 12/1/2010)

             I hope that's clear enough.

            regards and thank you for your help

            Elie

            • 3. Re: Auto create records + D.down lists
              philmodjunk
                

              You can do this with a script that captures your ID value in a variable, switches layouts, creates the records using the captured ID to link it to your parent record and then switches back to your original layout:

               

              Freeze Window

              Set Variable [$ClientID; Value: YourTable::YourClientIDField]

              Go To Layout [//select a layout that refers to your maintenance dates table]

              Set Variable[$Month; Value: 1]

              Loop

                New Record / Request

                Set Field[maintence dates::YourClientIDfield ; $ClientID]

                Set Field[maintence dates::YourDateField ; Date ( 1 ; $Month ; Year(get (Currentdate) ) )]

                Set Variable [$Month ; Value:  $Month + 1 ]

              Exit Loop If [$Month > 12 ]

              Go To Layout [Original Layout]

               

              Notes:

              You'll need to use your table and field names in place of mine.

              If the year isn't always the current date's year, you'll need to store it in a global field and refer to it instead of Get (currentdate)

              • 4. Re: Auto create records + D.down lists
                Elie
                  

                Thank you very much for your help, I will try that tomorrow because it's getting late here :D

                but just one thing for tonight, the value list seems to work and it's showing related value, but only one ( the first ), Am I  missing something ??

                • 5. Re: Auto create records + D.down lists
                  philmodjunk
                    

                  Elie wrote:

                  Thank you very much for your help, I will try that tomorrow because it's getting late here :D

                  but just one thing for tonight, the value list seems to work and it's showing related value, but only one ( the first ), Am I  missing something ??


                   

                  Probably, but I'd need to know the details of how you set it up in order to suggest what might have gone wrong.

                   

                  You'd need to list out:

                  1) layout's table occurrence

                  2) settings in value list definition.

                  3) relationship between the two table occurrences involved

                   

                  Even then I might not be able to tell as you have to set things up correctly on your layout also.


                  • 6. Re: Auto create records + D.down lists
                    Elie
                      

                    Ok, I'm using a layout called service call that contains a field called client name, and another called client contacts and many other detail fields, what i need is to have a value list in the contact's field that contain strictly the contact's names of the client that i've entered his name in the proper field.

                     

                     I've created a relationship between the [Contacts::Contact Name] = [Service Call::Contact Name] and i used your help to add [Contacts2::Client's Name] = [Service call::Client Name]

                     

                     Then in the Service call layout i've entered in the field Contacts to the field control sectio, Display values from (manage list) ... Specify field: Use values from first field: [Contacts::Contact's Name], Include only related values starting from [Contacts2].

                    I really appreciate your help.

                    Regards.

                     

                    • 7. Re: Auto create records + D.down lists
                      philmodjunk
                        

                      "Then in the Service call layout i've entered in the field Contacts to the field control sectio, Display values from (manage list) ... Specify field: Use values from first field: [Contacts::Contact's Name], Include only related values starting from [Contacts2]."

                       

                      set up your value list as Use values from "Contacts::Contact's Name", starting from "Service Call".

                       

                      The new relationship you've created shouldn't be needed for this.

                       

                      How it works:

                      Filemaker looks in the first table occurrence (values from) to find the values to list in the value list. It then uses the second table occurrence (starting from) to find the relationship and match value (Service Call::Contact Name) to figure out which values in the first table should be listed for the current record.

                       

                      A suggestion for improving your database design. Base your relationship on an ID number instead of a name field. People change their names, typos during data entry occur and if either happens, you have a problem as changing the client's name will break this relationship unless you also track down all the related records and update them before you change the field in your clients table.