creating new fields by splitting one

[LisetteG]

Hi all...

I have just started to explore the wonderful world of calculations in FMP. I hope that you can help me with the following inquiries.

So I work for a educational grant organization and we have just started building a database for all of our past recipients. Right now I'm trying to figure out a way to split grant information for recipients who have received more than one grant.

This is a sample of the type or information and format that I have now:

Name: John

Grant Year: 1961/1999

Grant Category: Research/Degree Study

This is what I'm aiming for:

Name: John

Grant Year 1: 1961

Grant Category 1: Research

Grant Year 2: 1999

Grant Category 2: Degree Study

I did try out the calculation: LeftValues ( text ; numberOfValues ) for the Grant 2 field

But this doesn't work well for those records containing a single grant.

This is what happens to records with information for a single grant.

Before:

Name: Mary

Grant Year: 1961

After:

Name: Mary

Grant Year 1: 1961

Grant Year 2: 1961

So my questions is. Would their be a calculation that can utilize the '/' slash symbol. Something like (text ; numberofvalues; before or after '/')? or any other solution or suggestion?

I'd like to find a similar solution for the "grant category" field.

what I have:

Grant Category: Research/Degree Study

What I want:

Grant Category 1: Research

Grant Category 2: Degree Study

This might be very confusing. Please let me know if you need further clarification.