Take a look at the case and patternCount functions by looking them up in FileMaker help.
Case ( PatternCount ( Description ; "665" ) ; "Wages in/out" ;
PatternCount (Description ; "tesco" ) ; "Living expenses" ;
// add as many tests as you need in pairs like this.
Not sure what you want in the second case. If converting weights, £9 = 9 * 453.592 37 = 4 082.331 33 g not 200g. Hence my questioning what it is that you are actually computing here...
Well i'm not trying to convert weights but get weights from prices so if for example I have an item which is £9 I know i only stock one item at £9 and know the weight is 200 grams
I suppose maybe I could use the same
Case ( PatternCount ( Description ; "£9" ) ; "200g" ;
PatternCount (Description ; "16" ) ; "340g" ;
However I now have another issue, I tried creating a graph but it shows to many results as my dates come in as 01/01/11 then 02/01/11 and only want to summerise these records in to months and what "types" as one field have been going out in a month is there any way to do this?
Your Case functions might be very cumbersome unless you can set up a table of such matching values instead of a case function with a large number of such clauses.
There is away to produce such a chart, but its difficult to go into any great detail without knowing the structure of your table
make sure to import your dates into fields of type date, not text.
This calculation will take such a date and return the date of the first day of the same month: DateField - Day ( DateField ) + 1
If you sort your records on such a calculation field, you can group them by month for your graph.
Then you can use the "use data from current found set" and "show data points for groups of records when sorted" options to chart your data with points/bars for each month.
Ok almost there on the report, this is helping a lot and i thank you for your time.
I have one more issue for example I now have a load of transactions which are now neatly oraganised in to months and also "types" using the pattern calculations, i now was to present this in a graph so I can see in a graph format what the main types are.
I thought maybe I sum up the amount of types there are but i can't figure out how that would work as they are all on different dates, any ideas?
Will this be a bar chart with a bar for each item, further grouped by month? or some other format?
You can sort your records by month, then by item to group them in that fashion.
Well say for September I want one graph which would be a bar or pie that shows all types for that month etc
One graph for the month can be done if you perform a find only for records from that month. It can also be set up in a table of related records where you have just one record for each month. I think you can also set this up by putting the chart in a sub summary part, "when sorted by" month. In the last case, you'd sort them like I described earlier, sorting by month and then also by Item type.
I'm very new to filemaker pro and trying my best, i have tried to do a find only for 01/09/11 so it brings up only whats in september but i must be doing something wrong, whats the best way to do a find only records for this type of data?
In what field did you enter this date. the cMonth field that I suggested or the date field?
It should have worked in the cMonth field. In the date field, you can use wild cards when performing the find: */9/11
and this should find all records for September 2011. 1/9/11...30/9/11 is a date range you can enter as well.
All of this asumes performing searches in fields of type date.
Hi Phil, I have tied to get the month to appear elsewhere but it won't work, I tried doing it as a pattern to reconise "01/09/2011" and change to "september" but that did not work, however the previous way to change the date so it was just 01/09/11 worked fine, "month: DateField - Day ( DateField ) + 1"
I've been trying to basically come up with a way that when my records are imported it works out for example for
"Sep" have "Summary of types" for example "wages in/out" and have the "amount" with all of that "type" summed up.
Have this for a few months etc, just can't seem to get it working quite right, could you offer any advice?
I'm afraid that I can't follow your description of the problem in your last post. Where does this process fail for you?
A few thoughts:
On your layout, you can use the inspector's data tab to format a date field so that only the name of the month is displayed.
In a calculation, MonthName ( DateField ) returns the name of that month.
If you are importing dates in a text format such as "September 12, 2011", either a script or a calculation can convert the text into a date in an actual date field in your table.