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.
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'
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.
So, the numbers change when I import an excel file into filemaker which adjusts all the numbers.
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?
Makes sense. Hmm.. I've done a little bit of scripting, but really just very basic stuff.
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.)
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.
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.
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.