12 Replies Latest reply on Jul 29, 2010 11:16 AM by philmodjunk

    value list from calculation field



      value list from calculation field


      I've moved to Filemaker from Access/mySQL and am having a few problems getting used to the way Filemaker deals with relationships between tables.


      I have three tables: Clients, Companies and Jobs

      Multiple clients can belong to the same company, so the Clients table has an intCompanyID field to relate it to the Companies table (which stores company name, address, etc)

      Each record in the Jobs table has an intClientID field which relates to the client's name (Clients table) and the company name (Companies table through it's relationship with the Clients table).

      I want to display the client's name and company together, as one field. I have done this by creating a calculation field in the Clients table which references the Companies table:

        txtClientName & " (" & Companies::txtCompanyName & ")"

      This works fine in a list view for the Jobs table.

      However, I would like to have a layout using the Jobs table to define a new job, and to be able to select the client name (with company name) from a dropdown list. I try to make a value list from the aforementioned calculation field but I get this error: This value list will not work because the field cannot be indexed

      Any idea how to get this to work? Am I even on the right track in using a calculation field?

        • 1. Re: value list from calculation field

          By definition, any calculation field that refers to one or more fields from a related table will be unstored. Unstored fields do not have an index and since filemaker value lists that specify fields as their data source actually draw their values from the field's index, there is no index available to produce your value list.

          You can, if you wish set up a two column value list with column 1 being the client name and column 2 the company name from the related table. Of course, if you need column one for the ID value as is often the case, that won't work...

          • 2. Re: value list from calculation field

            I have now set up column one for the ID value, and column two as the client name - which does work, although I cannot add the client's company to the end of the name.

            In Access, I would either have: a three-column list - you cannot seem to do this in Filemaker?

            or I would define column two as the result of a query - again this does not seem to be possible?

            I am sure that anything I can do in Access, I can do in Filemaker - but this has me stumped. Perhaps I have approached the problem in the wrong way from the beginning?

            • 3. Re: value list from calculation field

              Filemaker is much more limited than Access when it comes to setting up value lists. I've used the suggest a feature form: http://www.filemaker.com/company/feature_request.html to say so in the past as this is one of the few areas where Access clearly offers more and better capabilities.

              The only option I can think of denormalizes your company name data. You could set up a company name field in clients that uses a looked up value auto-enter setting to copy the company name into the client table so you can set up a stored, indexed calculation that combines client name with company name. This creates issues for updating this data whenever you edit the company name in its original record in the Companies table. You'd need to use a script trigger or something to find the related records and update them whenever the company name might be changed--not the ideal approach.

              Sometimes when one hits a wall, it helps to step back and ask one's self whether there's a completely different approach possible. So I'll ask this question? What problem are you attempting to solve by appending the company name to the client's in the value list? I'm hoping a conditional value list approach might eliminate the need for seeing the company name in the value list all together.

              • 4. Re: value list from calculation field

                Thanks - your denormalizing suggestion works. This is what I was trying to implement in the first place; an indexable calculation field. However, as you point out, this fixes the field value rather than providing a reference back to the Companies table - which is very disappointing from such a seemingly powerful database!

                To answer your question: The company name is there because in my previous Access database, the clients list was sorted alphabetically by company and then by client name (i.e. all clients from a particular company are grouped together).

                I have just replicated this effect (almost) by putting the company name first in the calculation field, and the client name second. This means I can sort the value-list by company name, although the client names are unsorted (this is acceptable).

                I've not used conditional value lists before but after a quick googling, are you suggesting: I have 2 drop down boxes (fields). In the first field I select the company name - which then changes the value-list in the second field to contain only clients from that company? If so, then that would also be an acceptable solution - perhaps better because both fields could be sorted alphabetically.

                • 5. Re: value list from calculation field

                  Thanks for your help.

                  I have now changed all my layouts to use 2 fields instead of 1. The result is slightly less aesthetically pleasing layouts, but it is far more flexible than using stored (indexed) calculations.

                  • 6. Re: value list from calculation field

                    I am having a slight issue now - cosmetic more than anything.

                    I can select a company from a pop-up menu in the first field. This affects which clients are being displayed in the pop-up menu of the second field.

                    But when I select a client, the value being displayed in the first field changes from txtCompany (which is the 2nd column) to intClientID (1st column).

                    I guess this happens because the company field is set to display data from Jobs::intClient and it is updating when the client is selected.

                    is there any way to have a pop-up menu show values from a value-list but not be linked to an actual field in the table?

                    • 7. Re: value list from calculation field

                      I'm not sure I follow your description. A field need not be linked in any way to the table that serves as the source of values for the value list other than the value list format itself.

                      I don't see why the value in field 1 would change when you select a value from the value list on field 2 unless that's a data field from a different, related table. If all else fails you may want to upload a demo file to a file sharing site and post the link here to the thread so I can take a look at what you've set up.

                      • 8. Re: value list from calculation field

                        I have uploaded my database here: http://www.mediafire.com/file/5uw8zeta8aiw8x5/data.fp7

                        I would be very grateful if you could take a look at it!

                        The "new job" layout is where the problem is. I do not think I am using the value lists correctly... I have 2 pop-up menus that display data from the same field, but are populated with data from seperate value lists. I am sure this is not the correct way of doing things but I could not figure out another way of achieving my aim!

                        • 9. Re: value list from calculation field

                          Yes, using two copies of the same field with different value lists won't work as you are entering two different values into the same field. You need two fields, one for each value list.

                          I've uploaded a fixed copy of your file to: http://www.4shared.com/file/1y0RoLTo/ConditionalValueListProblem.html

                          I first defined a global field, gSelectedCompany to use as the first value list formatted field. I could also have used a local text field for this, though you need to be careful to treat the field solely as a filter for your value list as it won't update if you edit a company name in the company table.

                          I made two new table occurrences in your relationship graph. THese table occurrences point to the same data source tables as Companies and Contacts. I selected each of these table occurrences in the graph and clicked the button with two plus signs, naming the new TO's SelectedCompanybyName and SelectedContacts and related them like this:

                          Jobs::gSelectedCompany = SelectedCompanybyName::txtCompanyName
                          SelectedCompanybyName::intCompany = SelectedContacts::intCompany.

                          I formatted gSelectedCompany with a value list that lists all names from txtCompany in the Companies table occurrence.

                          I formatted intContact with a two column value list: Col1: SelectedContacts::intContact; Col2: SelectedContacts::txtContactName. I selected "Show only related values starting from Jobs".

                          • 10. Re: value list from calculation field

                            Upon further thought, I'd change the storage for selected company to local storage. I just noticed that with this as a global field, switching to a record where the contact is drawn from a different company messes up the display of the popup menu for intContact.

                            • 11. Re: value list from calculation field

                              Thank you, the solution works perfectly!

                              To me, it seems counter to good database design to create extra fields in a table or extra table instances/relationships purely for the purpose of implementing a GUI - possibly this is why I was having difficulty -but if this is the way FileMaker works then I will have to get used to it I suppose!

                              Again, thanks for your help.

                              • 12. Re: value list from calculation field

                                Filemaker gets very "layout specific" in how it does things and you often have to devise special purpose fields to implement one or another layout feature. The key thing is to not use such fields as having "real" data, but just as placeholders to get a particular widget to work. In our example, it may store a company name, but it's not the "real" company name stored in the other table and should not be treated as such. It's just an interface widget.

                                I did have the idea that you might set an OnRecordLoad script trigger to pre-load this company name field with the related company name so that the user doesn't have to select a value in it. If that works for you, you can even remove the field from the layout and let the script do it's thing behinde the scenes.