1 2 Previous Next 17 Replies Latest reply on Jun 3, 2016 6:28 PM by joelaw

    lookup not responding as expected?

    joelaw

      I am just starting and need help.  Many hours spent without proper result.

       

      I am trying to build a Church database where I have designed a very nice Membership table and a Offering table.  I enter first name, last name into membership table and then calculate to pair the names together.  Since we have many people with the same last names I need to know which person has made an offering to account for amounts total.

       

      In the Offering table I need to lookup the calculated field to use for the name.  I link the calculated names field to offering name.  When I enter a offering I must have both the last name and first name to see which person I need.  When I lookup this field I get a "?" instead of the name I want.

       

      I can do a lookup on any other field in the Membership table and it is correct but when I try to see the calculated field it doesn't work as expected.

       

      Cannot figure out what I am doing wrong?

       

      Joe

        • 1. Re: lookup not responding as expected?
          erolst

          joelaw wrote:

          I can do a lookup on any other field in the Membership table and it is correct but when I try to see the calculated field it doesn't work as expected.

          At a guess, it is because you're changing the very field that you are using as a match field for the relationship via which the Lookup happens – or because you're trying to relookup a calculated field, which is a) not even possible, and b) unnecessary, because the calculation would change itself.

           

          But actually, you shouldn't perform any Lookups – in a relational database system, you usually reference data via relationships, and don't copy it (with a few exceptions).

           

          joelaw wrote:

          Since we have many people with the same last names I need to know which person has made an offering to account for amounts total.

           

          That is why every table should have a primary key that is auto-entered and meaningless. Then it doesn't matter how many people share their last, or even their full name, or if you need to correct a typo in the name – the identity of the person is not dependent on these data.

           

          Also, if you have an Offering record that is related to a Member record (via a foreign key that links to the Member table primary key), there is no need to copy (which is what a Lookup does) the name into an Offering field; you can simply display (put on the layout) the related name field from the Member table.

           

          If you change it in the Member table, the display in a related table will change, too (because it is the same field).

          • 2. Re: lookup not responding as expected?
            joelaw

            Thanks for answer but now I really am going in circles.

             

            When a member gives us a check I need to lookup that member by Last name, First name and if found add donation amount and a catagory.  If not found, I then need to add a new name but not necessarily a member and add donation amount and catagory.  Somehow I have to account for the names, amounts and catagory of each donation.

             

            I don't really need to copy from membership just input which member I have.  An ID field does that OK and I have tried using this.  I don't mind if I have to "Copy" the field data and store to Offering table just so I can produce reports that tell me how much was donated by a certain person for the end of year.  It is not just adding the amounts to catagories and print the result totals later.

             

            I still cannot picture just how you are saying to do this in layout?

             

            Joe

            • 3. Re: lookup not responding as expected?
              greatgrey

              One possibility is that the calculated name is not matching the member lookup name, i.e. it gives "TomJones" when you need "Tom Jones" , "Jones Tom" or "Jones, Tom".

              edit also watch out for extra spaces, at the beginning or end and double spaces where single spaces are expected.

              Trim(firstname) &" "& Trim(lastname) will make sure there are not extra spaces.

               

              and I'm sure you have verify that your relationship and the key (calculated) field use in the lookup field are the right ones.

              • 4. Re: lookup not responding as expected?
                GaryTate

                Never use a key field that is used for anything else.

                 

                Set yourself up a sequential number fields in each table and then use these field to tie the records together.

                 

                G

                • 5. Re: lookup not responding as expected?
                  erolst

                  greatgrey wrote:

                   

                  One possibility is that the calculated name is not matching the member lookup name, […]

                   

                  You simply shouldn't have to do all that.

                   

                  Create unique primary keys, use them (and foreign key fields) in your relationship definitions, then forget about them.

                  • 6. Re: lookup not responding as expected?
                    greatgrey

                    Where the problem is the entering of new data, and it's not matching existing data when it should.

                    i.e. the name on a check is entered and wants it to find if the person is already in the database.

                    • 7. Re: lookup not responding as expected?
                      erolst

                      greatgrey wrote:

                      wants it to find if the person is already in the database.

                       

                      So search at the (only) place where that name is stored: the Member table. If you find several members with the identical name, use HumInt to find the right one.

                       

                      How exactly you do that depends on your workflow.

                       

                      It's easiest from the Member table – search the member by name; if found, good, if not, create a new one; either way, now simply use a portal to enter a new payment.

                       

                      But even from the context of the Payment (Offering, Donation etc.) you could create a new payment, then use a global field to enter a name, and have FM look for matches and inform you e.g.: "Would you like to use existing Joe Blow #1, 2, or 3, or create new Joe Blow #4 (and directly add their primary key to the foreign key field of the new payment)?"

                       

                      And that background search, of course, would also be programmed to run in the Member table.

                       

                      Also note that searching a mirrored (calculated, looked-up) field in the Payments table isn't that reliable, anyway; what if that member exists, but never made a payment until now …?

                      • 8. Re: lookup not responding as expected?
                        erolst

                        joelaw wrote:

                        I still cannot picture just how you are saying to do this in layout?

                        Find attached a quick'n'dirty sample file.

                         

                        Note how no copying is going on, and how the Offering layouts (entry, report) simply use the related name.

                        • 9. Re: lookup not responding as expected?
                          joelaw

                          Thanks guys.  I fiddled with your answers and erolst's example all day and now everything is even worse.  I am going back to my original layout and see if I can clean it up.

                           

                          My problems start because we have used QuickBooks 2009 for 7 years and I don't want to change the way this thing behaves.  Entering data in a whole different way will confuse the 'old' people so I am trying to make it function somewhat close to what we use now.

                           

                          I still don't understand why I cannot lookup a field with a combined last name and first name and get that data into my offering table.  It should be an easy task to take two fields in a table, combine them and use the result to enter into another field in a second table.  If the names are not found then press a button and go to the add member table and come back with the new names to finish the entry.

                           

                          Thanks again,  I will keep trudging along until I find an answer.

                           

                          Joe

                          • 10. Re: lookup not responding as expected?
                            keywords

                            joelaw wrote:

                            I still don't understand why I cannot lookup a field with a combined last name and first name and get that data into my offering table. It should be an easy task to take two fields in a table, combine them and use the result to enter into another field in a second table. If the names are not found then press a button and go to the add member table and come back with the new names to finish the entry.

                            I suspect your frustration comes from the fact that responses have used "filemaker speak" which presumes a level of understanding you don't yet have down pat. Erolst has given you a demo file which in fact does all the things you want it to, but probably because you a still struggling with the key FM concepts you don't realise this. So here is a brief summary which might help you get your head around it:

                            *     The file contains two tables: members and offerings. This is the standard method of organising data in a database—one table holds member details, the other holds offering details

                            •     Each table contains an ID field. In this case this uses the FM function, Get(UUID), which inserts a code (e.g. 6BC259A6-29FA-4351-8802-112C95200728) that is unique to the single record it belongs to. Many developers, including me, like to use this function to create unique record IDs not least for the very fact that it is meaningless data, which is how you should view the recordID. This field should be used by your FM file structure to create relationships between tables to ensure your relationships work to deliver the result you expect. It is an underlying field, not part of the human readable/human meaningful data your tables contain. Notice that erolst has not even included the field anywhere on any layout, as its presence is a functional thing only to drive your database relationships. In Filemaker speak, this field is often referred to as the primary key—i.e. the field which records from other related tables can match to.

                            •     To make a relationship between an offering record (the money donated) and a record in the member table, the offering record must have a field in which you enter the relevant ID—notice that in the demo file there is a field called id_member, for this purpose. In Filemaker speak, this field is used on the Offering side of the relationship between Member and Offering to match the offering record back to the member record of the donor.

                            •     Naturally neither you nor any user of the database should be expected to search for records by direct access to the record ID field. We all want, and expect, to use human readable data, in this example, a list of names. There are many ways to achieve this, and the method included in the demo is a nice neat one using a popup window—type Joe Blow, or just Joe, or just Blow, and click on the Search button and you will be presented with a list—in this case the two Joe Blows. Type just J and you will get a list contain the two Joe Blows pause Jane Doe. It's neat and works well. Furthermore, both the main layout and the popup contain a neat means for you to create a new record and assign the current donation to that new record. This uses some underlying FM functionality which you don't necessarily need to fully grasp right now in order to get on with things, but should study at some stage once you've got the hang of the basics. Try adding a new donation and then assigning that to a brand new person you create and you will see.

                            One thing that occurs to me is that this method uses popups, which are a recent feature of FM. If you are using an earlier version this may not be an appropriate method—what version of FM are you using?

                            • 11. Re: lookup not responding as expected?
                              karina

                              Is it possible that you save a copy as a clone and upload it?

                              Or show us your relationship graph?

                               

                              For the next time, you can always make a backup first, and if you're not sure how your changes you want to make will efect the solution try it on another backup first. Then you'll never make things worse.

                              • 12. Re: lookup not responding as expected?
                                joelaw

                                Karina, I don't know how to save as a clone.  Do you mean to send the complete tabels?  The relationships are Member ID to Offering ID only.  I am using File Maker Pro 15 with Windows 10 Pro 64 bit.

                                 

                                Keywords, thanks for speaking in "real" talk.  I always go back to teachers have to tell beginners to "Press the 'Enter' key after a command".  Sometimes it is obvious but to people who don't understand, the obvious in not always clear.

                                 

                                I have been through the whole example code many times but this does not get me where I want to go.  It works but adds about 5 keystrokes to each entry and when you have to do 200-300 entries that is too much.

                                 

                                As stated, I am trying to copy the way Quickbooks enters data.  For example:

                                 

                                Field- Received From.  Enter "L" and I get a drop down with all names with the letter 'L'.  Not very useful.  Enter "LA" and I get 'Lawrence, xxx', 'Langley,xxx', 'Langston,xxx', 'Laura xxx', etc.  I can then choose which one I want.  If none of these are right there is one more line at the top of the drop down called <NEW> which will take me to the membership table and I can enter a new member.  When that gets saved it goes back to the offering table with the new entry and moves to the amount field.

                                 

                                Very simple and straight forward without much hassle and many key strokes.  If I set the Received From field to do a lookup on any field in the membership table it does the job.  If I set the lookup to the calculated field "Full Name" which if calculated I get only a "?".  Why can't FMP allow me to do a lookup of a calculated field the same as a regular field?  From the offering table, their should not be any difference?  If QuickBooks can do it why can't FMP?

                                 

                                Joe

                                • 13. Re: lookup not responding as expected?
                                  joelaw

                                  Is there another way to combine two text fields into another text field like "full name = last name & ", " and first name?

                                   

                                  I use a calculation field but maybe I would get what I want if I could just combine the fields.  It would solve my problem.

                                   

                                   

                                  Joe

                                  • 14. Re: lookup not responding as expected?
                                    karina

                                    Hey Joe,

                                     

                                    See the attached file.

                                    I hope this helps you a bit further.

                                     

                                    Greetz

                                    Karina

                                    1 2 Previous Next