6 Replies Latest reply on Dec 31, 2011 6:05 AM by MarkJohnson1069






      I have a database with 20 fields labeled "category 1", "category 2" etc. I want to be able to access the correct field based on an input of 1-20. Can someone help me with a simple script?

        • 1. Re: Scripting

          This can be done, but the field names suggest that you should change your table structure by defining a table of related records where "category 1", "Category 2", etc. are replaced with individual related records.

          To answer the specific question:

          If you input a number into a global field, you can use GetField and SetFieldByName to access your data. GetField "reads" the data in the specified field and Set Field By Name modifies the data in the field.

          Example: GetField ( "YourTableName::category " & YourTable::GlobalField )

          will return the contents of your category fields.

          Set Field BY name [ "YourTableName:: category " & YourTable::GlobalField ; 5 ]

          Puts the number 5 in the specified category field.

          • 2. Re: Scripting

            Tried it, however a "?" shows in the field. The actual fields are "Cash Percent 1" thru "20" & "Check Percent 1" thru 20 in a file called "Customer Type". The script I have is:

            GetField(Customer Type::"Invoices::Payment Method " & "Percentage" & "Related Products::Category")

            • 3. Re: Scripting

              What's the actual name of the table and field?

              Is the current layout based on Invoices or Related Products?

              What is the relationship between these two tables?

              • 4. Re: Scripting

                "CUSTOMER TYPE"

                Type ID

                Cash Percent 1

                Cash Percent 2...20

                Check Percent 1

                Check Percent 2...20


                "CUSTOMER FILE"

                Used from sample with CUSTOMER TYPE added


                "INVOICE & LINE ITEM FILE"

                Used from Sample


                PRODUCT FILE

                Used from sample, but added product category with values of 1-20 (to match the field from customer type). Linked to "ITEM FILE"


                During invoicing "Payment Method" a radio button with "Cash" "Check" add to the "INVOICE" File. The customer type is pulled from "Invoice Billing Customer" File from sample


                So, based on the selection of cash or check and the product category, I need the get the value in the right customer type field.


                Hope it make sense. Thanks for your help

                • 5. Re: Scripting

                  As I mentioned before there are better ways to structure this data by using a related table with records for each value instead of one field for each value.

                  "used from sample" doesn't really tell me much as there are many starter solutions provided with FileMaker. I need to know what relationships if any exist between the tables (not the files).

                  During invoicing "Payment Method" a radio button with "Cash" "Check" add to the "INVOICE" File. The customer type is pulled from "Invoice Billing Customer" File from sample

                  What layout are you on? In layout set up for this layout, what table occurrence is specified in Show Records From? In what table is Customer type defined?

                  • 6. Re: Scripting

                    Works Great. Thanks