11 Replies Latest reply on Sep 27, 2011 11:27 AM by philmodjunk

    working out calculations



      working out calculations


      I need help, I have been looking at Bento 4 or Filemaker pro to sort out a few CSV documents and organise things. However I seem to have the problem when it comes to working out calculations. 


      What I want to do it for example:


      I have a field called "amount" which shows an amount. I then have a field which is called "description" which shows where the payment is from, what  I want is to create a multi-choice option field called "type" however instead of me clicking in each one and selecting the type I want it to work out the type by the description for example:


      If the "description" field contains "665" I know if "wages in/out" 


      If the "description" contains "tesco" its "living expenses


      I have been trying to find a way to set this up in both programs and can't find a way, i also need the same answer for a similar project in which I need it to work out from the item title what the weight is or from the price work out the weight so say:


      If £9 needs to equal 200g


      Please help as i'm very new to this and seems to be taking a lot of my time trying to work out the best way of tackling this.

        • 1. Re: working out calculations

          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...

          • 2. Re: working out calculations

            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" ;

            etc etc 

            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?

            • 3. Re: working out calculations

              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.

              • 4. Re: working out calculations

                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? 

                • 5. Re: working out calculations

                  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.

                  • 6. Re: working out calculations

                    Well say for September I want one graph which would be a bar or pie that shows all types for that month etc

                    • 7. Re: working out calculations

                      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.

                      • 8. Re: working out calculations

                        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?

                        • 9. Re: working out calculations

                          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.

                          • 10. Re: working out calculations

                            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?

                            • 11. Re: working out calculations

                              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.