filemaker - value list question

jon-e-orange

hey hey hey.



I am building a invoicing section on to out filemaker 7 database.



We use a table called "time list" with the following fields:



Job No (this is the primary key for all our db's), date, work done, who, task (a value list containing 3 options: design, artwork and admin) and time.



I have made a table called "breakdown" on which I want to divide in to the 3 options from the value list. So we know how much time was spent on design, artwork, admin.



any suggestions on how to do this?



Thanks



jon-e

karl kuehn

Ok... this is one of those places where FileMaker is a bit off the beaten path. Once you get the ideas it is very doable. First you are going to need to create a few more fields in the database (filemaker's name for tables):



The first field is a concation of "job number" and "task", a calculation with the formula 'jobNumber & "_" & task' would be good (assuming that neither will ever have a "_" in them).



Then you need to create a match field for each value of task with a formula like 'jobNumber & "_design"' so that we have something to match against.



Next create a series of relationships back to the same file between the match fields you created in the last step and the concation field from the first step.



Finally create a series of calculation fields, one for each value of task that sum the value of "time" through the relationships that you established in the last step. The formula will look something like:



sum(relationshipName::time)



That should do it. Not a nice process if you have a lot of values, and you have to think about how many values you have (as there is no quick way of changing this in one place).



There might also be better methods using the variables available in 7, but I am stuck supporting MacOS 9 clients, so am still on 6.