A Year to date sum field

[Jaser247]

Hello all,

I currently have a software db with all the software my company has. I have added price db and set up a relationship between them both. Inside the software db I created a portal for the price db to show up. I need some year to date field that has the total cost of what I spend/spent for the year for each idividual piece of software. What type of calc can I use for this? I have been struggling with this for a while and it's been a bit hard since I'm new to FM.

Thanks,

Jay

[Razumovsky]

There are several ways you could do this. I would suggest a concatenated key relationship that would filter your portal (run a search on "concatenated key" and "filtered portal" on this forum, there are lots of posts about this).

In a nutshell, you would create a stored text calc field (c_YTDKey) in your Software db that would be

SoftwareName & " - " & YTDYear

where YTDYear is a global text field with value list of all years you would need to look up.

In your Price DB, you would create a complimentary calc field (c_YTDKey) that would be:

SoftwareName &" - "&Year

where Year is a field that stores the year the purchase was made.

Create a relationship between these two from Software to Price DB. Put a portal based on this relationship on your Software Layout, and put the YTDYear above it. When you select the year from the value list, the portal should only display costs associated with that software from Price DB that shares the same Software name and Year.

another field could be placed at the bottom of the portal (c_YTDSum) which would be :

Sum(YourNewRelationship::Price)

which would give you the total.

Make sense?

-Raz

[Jaser247]

Thanks, I will try this, and let you know how it goes.