3 Replies Latest reply on Nov 4, 2013 8:52 AM by Mike_Mitchell

    Self-populating tables

    memanic

      Hi everyone! Newbie here! I'm working on a solution to a donation inventory problem for a non-profit food pantry using FM 10 Pro. I have a main table (data entry) with the "item name" and "cost." This looks up from another table (donations) to find the item and copy the price into the "price" field on the data entry form, then use that for a calculation. Is there any possibility that I could set it up so that the field on the data entry "item name" would look for it on the "donation list" AND, if it is not found, copy the information as typed on the "item name" and "price" back into the "donations list," thereby populating the "donations" list with any item not present at that moment.

       

      I realize that this is causing data to flow both ways. Is that possible???????

       

      Thanks,

      Pat R.

        • 1. Re: Self-populating tables
          Mike_Mitchell

          Hello, Pat.

           

          Yes, what you want to do is theoretically possible. You could set a Script Trigger (probably OnRecordCommit, since you're using two fields) to create a new record in your library table if the item isn't there already. However, doing so on an automatic basis is fraught with issues.

           

          The first issue is simply the fact that people make mistakes on data entry. They mean to enter, "Bush's Baked Beans" and enter "Bushs Baked Beans" instead. The machine doesn't know they're supposed to be the same thing, and poof! You wind up with a functional duplicate in your library table. Bad.

           

          The second issue is that the whole purpose of a library table is to control data entry, to restrict it to approved items. If users are permitted to enter whatever items they want, willy-nilly, then you might as well do away with it and just do a self-join to the items table.

           

          Allow me to suggest a better approach. Rather than automatically adding the data to your library table, you could have the system query the user as to whether or not the missing item should be added. Then, it could be marked for addition by a data admin later, who would have the privileges to make the decision as to its propriety.

           

          Also, rather than allowing free text entry, consider implementing a value list or a list-view-based selection method. This will tend to cut down on spelling / typo errors.

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Self-populating tables
            memanic

            Thanks Mike! You are absolutely on target with your analysis. I will look at both suggestions of solutions. I am a little concerned with the list view-based selection as there will be hundreds, if not thousands of entries. I like that solution better if the entry of 'beans' brings up all the 'beans' entries for selection. I'll check it out. It seems to be the more elegant and simpler way to solve the problem.Also, would cut down on Admin time. thanks again for your time!

             

            Pat

            • 3. Re: Self-populating tables
              Mike_Mitchell

              When dealing with the long lists, you can implement a filtering feature that performs a search based on the contents of a global field to thin the list. This will accommodate longer lists without so much scrolling.

               

              Mike