10 Replies Latest reply on Dec 26, 2012 9:54 AM by philmodjunk

    Please help!

    Starmonger

      Title

      Please help!

      Post

           Hello,

           Is it possible to have a calcuation so that when the result in a field changes from 0 to another number, it will do one thing, but if the number changes from another number that isn't zero, it does something else? Basically currently I have this:

            

           If ( Quantity=0; "End" ; "Revise" )

           Which I'm using for Ebay listing revision. So I upload this file to Ebays file exchange and it will end acutions that are 0 quantity, and change the quantity (revise) for any that arn't 0. But, I need a third option which is 'relist', for any that have gone from 0 to a number above 0. Does anyone know how I can possibly do this?

            

        • 1. Re: Please help!
          philmodjunk

               You can use Case instead of If for cases where you need more than two different values returned. I'd post an example, but can't quite figure out how the different quantities are supposed to match up with values returned.

               I need a third option which is 'relist', for any that have gone from 0 to a number above 0

               That option would seem to use the same values for quantity that currently result in "Revise". I can't tell from your post how the value in Quantity can be used to distinguish between "revise" and "relist" results.

          • 2. Re: Please help!
            Starmonger

                 Hi, thanks assising. So here's how it should be:

                 If the field changes from 0 to anything higher, the result is 'relist'

                 If the field is 0, the result is 'end'

                 If the field is any number other than 0, but hasn't just changed from 0, the result is 'revise'

            • 3. Re: Please help!
              philmodjunk

                   That would require being able to determine the previous value of the field--not something a calculation field can readily do. How does a value get changed from 0 to some other value?

                   If you are editing the value in FileMaker to make this change, a script trigger might be used to effect this result. If the data is being changed outside of FileMaker and then imported into the database, you'll need to set up something that compares values before using the imported data to update the previous values.

              • 4. Re: Please help!
                Starmonger

                     So, the numbers change when I import an excel file into filemaker which adjusts all the numbers.

                • 5. Re: Please help!
                  philmodjunk

                       Which means that you would need to import the data into a different table first and use a script that loops through the records updating a "changeFlag" field to identify the records where the Quantity has changed from 0 to nonzero so that a calculation can correctly identify the "relist" records.

                       Have you done any scripting before?

                  • 6. Re: Please help!
                    Starmonger

                         Makes sense. Hmm.. I've done a little bit of scripting, but really just very basic stuff.

                    • 7. Re: Please help!
                      philmodjunk

                           90% of FileMaker script steps show their "macro" heritage in that there is a corresponding menu option that does the same thing. Thus, you can often work out what works manually, making a note of what menu options you selected and then you can recreate the process as a script adding in the few "control" steps such as IF, or Loop where needed.

                           The starting point for this is whether you have a field (column) in the imported data that uniquely identifies each entry so that you can set up a relationship between that field and the same field in your original table. That allows for a one to one "match" or relationship such that a script can loop through the imported records and compare the value of the quantity in the imported data to the quantity in the same record of the original table.

                           Do you have such a field?

                           Do you know how to set up such a relationship?

                           (With FileMaker Advanced, you can use Copy to copy and Paste a table to produce a duplicate table for your imported data. In regular FileMaker Pro, you can use import records with the New Table option to duplicate a table.)

                      • 8. Re: Please help!
                        Starmonger

                             Yes I have such a field, it contains each products item id, and yes, I think I know how to set up this kind of a relationship also.

                        • 9. Re: Please help!
                          Starmonger

                               So if I copy the table, then the new one I link the ItemID field with a relationship '=', is this correct? I can't seem to import records into that new table, it is just empty and won't let me choose it as a destination when I go to import the csv file. I have a layout configured to show the records in the new duplicate table.

                          • 10. Re: Please help!
                            philmodjunk

                                 When you use Import Records from the File menu to import records, first select a layout based on the table into which you plan to import the records.