10 Replies Latest reply on Apr 21, 2017 8:06 AM by hottonem

    Auto-Enter Fields Help

    hottonem

      Hello,

       

      I don't have to edit our FileMaker often so bear with my ignorance, please!

       

      I have to enter quite a few invoices on a daily basis into our database, and I'd like to try and set up the invoice form to auto-enter some fields and save me time.

       

      Basically, when I choose the location from a drop-down menu (which is text), I'd like the corresponding number fields of Customer ID, Cost Centre, and Fund Centre to populate.

      New Invoice.PNG

      Ex. If I choose "Location 1," I'd like the ID/Customer No., Cost Centre, and Cost Fund Centre fields to automatically populate with the data I've predetermined. Location 1 = ID 1, CC 1, and CFC 1. I've never used calculation fields before or have had to set up scripts, but from what I was trying (from reading the forums, nothing seemed to be working for me).

      Field Calc.PNG

      Going into Manage Databases - Fields - ID Number - Specify Calculation, I tried several things but always got error messages. I tried a few of the "Get" formulas, and thought maybe the "If" formula could work--but I'm obviously not doing them right. I either get the above error message, or one about not having enough "(" or ")" in my calculation.

       

      Any help would be much appreciated! Again, I don't use calculations much at all so explanations would be wonderful. Just trying to make the day-to-day more efficient and prevent simple entering errors!

       

      Thank you,

       

      Emily

        • 1. Re: Auto-Enter Fields Help
          philmodjunk

          What you describe assumes that any given location uniquely identifies exactly 1 customer, Cost center and Fund center. That seems very unlikely. Are you sure that this is really the case?

           

          Assuming that it is, you need a table of Locations where the location name (or an associated ID field) is unique with a field for Customer ID, Cost Centre and Fund Centre.

           

          The auto-enter expression for Invoices::Customer ID then is simply:

           

          LocationsTable::CustomerID

           

          The expressions for the other two fields are the same in format, but name different fields in the locations table.

           

          The Looked up Value auto-enter option could also be used to enter these values each time the location field's value is modified in the invoice record.

          1 of 1 people found this helpful
          • 2. Re: Auto-Enter Fields Help
            hottonem

            Thanks for you quick response!

             

            Yes, this is the case. Each location has a unique customer number, cost centre and fund centre.

             

            I will try to play around with making a table--thank you for your help!

             

            Emily

            • 3. Re: Auto-Enter Fields Help
              philmodjunk

              And I forgot to add that this locations table needs to be linked in a relationship to your invoice table by the location fields.

              • 4. Re: Auto-Enter Fields Help
                howardh

                hi,

                I use some guidelines which in my opinion work best.

                As Phil mentioned to create a new table which will have the id fields you need to populate with.

                then make a relationship with the table occurrence that you are in to the new table with the id fields.

                 

                Instead of using a lookup, I use a catculated auto enter.

                You can see it right above the lookup button in the field options panel.

                enter the field name with the relationship (table A related to table B)  [A_B::id]

                so that on a new record creation the id number that you want gets populated in the field you want in the local table.

                1 remember that the field that the relationship is based on in the daughter table must be indexed.

                2 in order for the auto enter to work, the relationship must be valid, meaning the field in the parent matches the field in the daughter table.

                 

                The other option is to do it by scripting. I like scripting the setting of those fields because I find it most dependable. It takes less than a second. Second most dependable way is the calculated method(as explained above) and third most dependable way is lookup in my opinion.

                 

                I would say go with what is most comfortable for you.

                 

                Hopefully this helps

                 

                Howard

                • 5. Re: Auto-Enter Fields Help
                  annr

                  Note calculated auto-enter values have an option "Do not replace existing value" which defaults to On. Be sure to switch that off, or if someone accidentally selects the wrong location, the others won't update when it's changed.

                  • 6. Re: Auto-Enter Fields Help
                    howardh

                    annr makes a good point. If the id information in that field that you want populated will never change then check the box that says "Do not replace existing value of field".

                     

                    ty annr

                    • 7. Re: Auto-Enter Fields Help
                      hottonem

                      Thank you for your suggestions.

                       

                      As it turns out, I was able to figure it out without the creation of another table and adding relationships--which seems a little out of my league as it is now. I crafted a nested if function which perfectly suits my needs and works without the additional tables.

                       

                      Thank you everyone for your help!

                      • 8. Re: Auto-Enter Fields Help
                        philmodjunk

                        Umm, no it does not perfectly meet your needs sorry to say. It works just fine right now, but you have just added a "brittle" design feature to your database--a feature that easily "breaks" and thus fails to work correctly when relatively modest changes occur in the future--such as adding a new customer or changing the data associated with a given location. Any such change and you have to go in and make a design change to your system.

                         

                        By using a look up table, all such changes are managed through simple data entry operations. You the developer do not even have to be the person making the change as you can delegate that to an authorized user or group of users.

                        • 9. Re: Auto-Enter Fields Help
                          annr

                          If you have any spare learning time (I know, we all have so much), I recommend going through "FileMaker Pro 14: The Missing Manual" (my library had it in their tech ebook collection). I found it very helpful for filling in knowledge gaps and giving me ideas when I was learning FileMaker.

                           

                          --Ann

                          • 10. Re: Auto-Enter Fields Help
                            hottonem

                            I understand it has it's issues, but what I mean is it'll have to do until I can learn more and properly set up the look up table. I have a long way to go in understanding everything it takes to get it working, but at least in the mean time some of the work it cut out of the daily invoice entry! Which is a bit of a relief.

                             

                            This database was created by someone else a long time ago, and I'm sort of just trying to find solutions to things I think would make the day-to-day a lot easier. I'm by no means the developer (though I'm apparently the only one in the office who knows how to do any kind of minor edits!).

                             

                            Thank you for your help; I'm going to do some research on properly setting up the table and adding the necessary relationships.