7 Replies Latest reply on Apr 16, 2012 4:02 PM by disabled_JackRodgers

    Filemaker Pro 11 Script - Add fields dynamically?

    EricKornmeyer

      Title

      Filemaker Pro 11 Script - Add fields dynamically?

      Post

      So we use FMP11 to do inventory management. I do price updates to our products 3 times a week and it would be nice to store our past cost values into a separate table for historical pricing. I know how I would go about doing most of it, but is it possible to create a new field that is labeled as today's date on the fly? So my headers would be labeled with that days date and the old pricing value from my other fields would be inserted.

        • 1. Re: Filemaker Pro 11 Script - Add fields dynamically?
          Sorbsbuster

          Presumably you export the prices to an Archive Table.  (Although you could just leave them in the Price Table and mark them as 'Archived'.)

          You could create one field, a global date, and use that as the header for the column of records.  You could then create a relationship from the Live Price Table to the Archive Table using that data.  As you change the date you would see the other prices display from the archive table.

          • 2. Re: Filemaker Pro 11 Script - Add fields dynamically?
            philmodjunk

            Good advice from Sorbsbuster, but to answer the specific question, you cannot add new fields to a table via a script.

            • 3. Re: Filemaker Pro 11 Script - Add fields dynamically?
              EricKornmeyer

              Thank you for the replies. So if I am understanding correctly Sorbsbuster, I would have to create a new gobal date field every time I want to store the value in the 'history'?

              • 4. Re: Filemaker Pro 11 Script - Add fields dynamically?
                Sorbsbuster

                No, for example, you could:

                - have a listing of prices in your Price List file.  This will show Product ID, Description, etc, and the last column might be 'Price'.

                - duplicate the layout and replace the 'Price' field with the 'Price' field from the Archive Table.

                - for this purpose that relationship to the Archive Table would be:

                PriceList::ProductID = ArchiveTable::ProductID
                and
                gDateToView = ArchiveTable::ArchiveDate (or whatever is the date that specifies that particular batch of prices.)

                You can then change the value in the gDateToView field and it will show you the price history for that date.  You could of course have the two prices - live and archived - beside each other on the layout.  Then you could calculate price change percentages, etc.

                Feel free to add two dates, two relationships, and display two archived prices, etc ad nauseum.

                • 5. Re: Filemaker Pro 11 Script - Add fields dynamically?
                  EricKornmeyer

                  Ah, that's where I was lost. I don't just want the last price and the current price, I want a whole archived section of every price as I go on. So a month from now, I would have price values for each item 12 times (3 updates a week for a month) with some way to correspond the date of the update with the old value. And obviously the list of historical values would keep growing.

                   

                  So I would have to keep adding dates and relationships for every possible date I wanted to see? 

                  • 6. Re: Filemaker Pro 11 Script - Add fields dynamically?
                    Sorbsbuster

                    No.  If you want to see a bunch of say 6 at a time, you would create 6 relationships, and 6 global fields (with 5 probably calculated off the first one.)  The strength of the global date in the relationship is that it lets you 're-use' the relationships.  Change the dates and you see 6 different 'old' prices.

                    Another complementary way is to have a portal to the Archive Table, connected by ProductID only, and it will show you every price since time immemorial for that product.

                    • 7. Re: Filemaker Pro 11 Script - Add fields dynamically?

                      If the script says go to layout (a table view) you can add the field there. I haven't 11 open but in 12 after adding the field I can set its type, etc. Oddly the new field doesn't show up in the table layout mode but it does in table view mode. It can be seen in the field editor and will appear on new layouts.

                      This is one reason to avoid giving Full Access priviliges to everyone!

                      Consider this method scripted with manual input...:)