13 Replies Latest reply on Jun 23, 2011 9:11 AM by philmodjunk

    Replace Field with Value List



      Replace Field with Value List


      Is there any way to replace field contents with a value list? Essentially, I have a table of purchases, and each purchse pertains to a client. Now I want to create a table of clients because there is certain client specific information that I want to put on each purchase record, but I would rather enter it once per client rather than once for each purchase. I plan on using the client name to link the two tables together. I realize that I would be better off using a numerical primary key such as a serial number for this purpose, but the client names are unique, and there is a very small chance that any of the names will change. It is a chance that I am willing to take. I also realize that I could perform a find for each client and do replace field contents on that found set, but I would prefer to have a new table so that I don't have to enter the data for each new purchase.

        • 1. Re: Replace Field with Value List

          You could define a value list from the contents of the clients::Name field and then from your layout set the name field to be a drop down with auto complete.  That way as you start to type the clients name it will narrow the list down until you can select the one you want.  If the client info is from another table liked to the purchase table, all the other fields from the client table will then fill in with that clients data.  If it is a new client you would have to go to a client based layout and create a new client record.

          • 2. Re: Replace Field with Value List

            Thanks for the response. This would work, and in my case I have few enough clients that I can really just enter each one individually. My question was less practical in nature and more out of curiosity about FileMaker's functionality. I just find it surprising that FileMaker is able to distill a list of values from a number of records, yet it apparently cannot use that list to create new records in a new table. This seems like something that might be useful fairly often. Admittedly, if the database is well planned out from the start you should never need to do this, but it seems like people often want to take and old database that was originally designed for one purpose and then build on it to serve a new purpose.

            • 3. Re: Replace Field with Value List

              I'm not sure I understand what you are trying to do.  But if you have the same fields in both tables and are trying to get the fields in purchase to populate based on clients  - you can use the value list based on clients::name for purchases::name, then set the other client specific fields in purchases to lookups based on the client::name = purchases::name relationship and they would fill in automatically.

              Or you could just find the client on a client based layout, run a script that creates a new record in purchases and set field purchases::name to client::name, purchases::address to client::address, etc

              • 4. Re: Replace Field with Value List

                Forget about the related fields. What I am primarily curious about is whether I can populate a primary key field based on a value list. Essentially, I want one record per value in the value list, and I want that value from the value list to be my primary key. I could create a script with a loop that creates a number of new records based on the number of values in the value list and then sets a field in each recor based on each field in the value list, but I was wondering if there was a more straightforward way for FileMaker to do this.

                However, as far as I can tell, there is no way for FileMaker to automatically create multiple new records at once.

                I would even be interested in a way to create 1000 new records with a primary key field populated with the numbers 1 to 1000. I know that you can set it to auto enter the serial number each time you create a new record, but it seems like there is no way to create 1000 new records without hitting the new record button 1000 times or using a script.

                • 5. Re: Replace Field with Value List

                  Is this an invoicing system?

                  If so, sounds like you may need to add some more tables and relationships so that you have one record for each purchase transaction (Invoice) with a related table (LineItems) that lists each product purchased.

                  If this is the case, let me know and I'll explain in more detail as well as provide a simple demo file you can examine as an example of what I am talking about.

                  • 6. Re: Replace Field with Value List

                    Each purchase only has one product purchased. But some clients have made a number of purchases over time. I realize that it would have been better to set the database up with the clients and purchases in a parent child relationship, but I wan't the one who created the databse, and so now it has a whole lot of purchases. Essentially what I am trying to do is create a parent table for a child table that already exists. I actually just went through and created a client table and gave eah client a serial number called clientID, and then linked it to the purchase table by entering the correct clientID for each purchase. I also gave each purchase a purchaseID in case the purchases ever start involving multiple items. The purpose of my questions is more to understand what filemaker is capable of.

                    • 7. Re: Replace Field with Value List

                      Understood, but knowing the actual structure of your tables and relationships helps us to help you and should you have an inefficient structure, not suggesting a change just sets us up to help you produce a more "tangled" solution. Wink

                      From your last post, you have this relationship:

                      Clients::ClientID = Purchases::ClientID

                      To enter data "once per client" put this data in your Clients table, not in Purchases. Then your Purchases layout can include fields from Clients to display data about the client. Any such data need be entered only once into a clients record and it will be displayed on the purchases layout for all purchases made by that client.

                      • 8. Re: Replace Field with Value List

                        Yes that is what I am doing. I still would like to know whether there would have been a way for me to populate Clients::clientID based on values in Purchases::clientID. For example, if my predecessor had been entering values into the Purchases::clientID field before the Client table existed, would there have been a way for me to poplate the Clients::clientID field without entering in each client ID but rather based on a value list derived from the Purchases::clientID field?

                        • 9. Re: Replace Field with Value List

                          The sense that I am getting is that the answer to my question is "not without a script."

                          • 10. Re: Replace Field with Value List

                            You certainly could have used Import records to pull data from purchases and put it into clients, but ClientID needs to be a unique value in the Clients table, but is not a unique value in the Purchases table. That makes this kind of sticky to set up in an automated fashion without getting some errors introduced into system. You'd need a way to identify all the clients in the purchases table with 100% reliability. Names alone won't do as two people can have exactly the same name and the same person can even have different names at different points in time. Similar issues arise for looking at addresses, emails and phone numbers. People change them and two different people can have the same contact info.

                            Best compromise would probably have been to add a text field to your Purchases table and use Replace field Contents to populate this field using a calcualtion that combines client names and contact data together. Then add a matching text field with Unique Values, Validate always specified. Then, when you import, the duplicates will be omitted, but once you've imported the data, it'd still be a good idea to review the data to weed out records where you can tell it's the same client, but some small difference in one of the fields has kept the system from doing so automatically.

                            • 11. Re: Replace Field with Value List

                              Make back up copy first. 

                              If the original file had just the table purchases and you create a new table for clients.  You should be able to do an import into the new clients table from the old purchases table, just import the client specific fields and not the other purchases specific fields.  Then you could delete all of the client specific fields, except your key field, from the purchases table.

                              It may be easier to create a clone of the original, do the table, field and layout modifications.  Then do two imports, one for the clients table, one for purchases.

                              • 12. Re: Replace Field with Value List

                                Ooh, we're so close to getting the answer that I want. I was not aware that you could import data from one table to another without including duplicates. That is exactly what I was looking for. Is this possible in the regular version of FileMaker or just the advance version? I have FileMaker Pro 11, but not the advance version.

                                • 13. Re: Replace Field with Value List

                                  Also, you can use the two text fields temporarily in a relationship to assign Client IDs from the clients table directly to the Purchases Records in the Purchases table rather than having to do this manually.