10 Replies Latest reply on Nov 28, 2011 12:44 PM by paulspafford

# Calculation help with related table

Hi newbie with a problem iv been trying to fix for a week with no luck please help!!!

i have a form with a simple list with a customers first and second name. on the form i want to create a new field joining the first and last name into just a name field.

i have tried calculating from the related table (first name+last name) but the result is always 1 (it counts instead of displaying the text from these fields)

what am i doing wrong??? please someone help me.

i have attached an image of the calculation problem.

will

• ###### 1. Re: Calculation help with related table

Will,

You are on the right track ... In the related table use a calculation field with a text result.

first name & " "& last name   (there is a space between the quotes)

Chuck

• ###### 2. Re: Calculation help with related table

hi chuck,

thanks for answering me however i have done this but bento still tries to count the fields. if you check the image i attached i have the answer format as text but for some reason it still counts. i am trying not to put this calculation in the related table but in the new form instead. i can get numbers to add up, etc but cannot get text to work from related forms. starting to think its a bug in bento 4?

Will

• ###### 3. Re: Calculation help with related table

Try using a formula like this, here assuming that there are 3 fields and that Name_middle can be empty.

Name_first, Name_middle, Name_Last

Name_first &

if(Name_middle ≈ ""

; " " & Name_middle & " "

; "" )

& Name_last

≈ is "not equal to"

The same principle could be used for Name_title

if(Name_title ≈ ""

; " " & Name_title & " "

; "" ) &

Name_first &

if(Name_middle ≈ ""

; " " & Name_middle & " "

; "" )

& Name_last

• ###### 4. Re: Calculation help with related table

SORRY SORRY SORRY

I did not get the point: Bento. I rest my case.

But can you set the file type in bento. Since you get the result you may have choosen "number" or maybe "bolean". Try choosing type text. If that is available.

I just assumed that this is a FileMaker Developer area

• ###### 5. Re: Calculation help with related table

Hi Carsten

yeah i was looking at that like eh? ha

i have it set as text in the origional form and have the answer to be set in text but bento still tries to count it. please have a look at my attached image and check i have it as text ( starting to doubt myself lol)

thanks for the reply tho.

is this a bug?

• ###### 6. Re: Calculation help with related table

Hey Will,

You need to remove the "Count" that you have before each field in the calculation.

Paul

--

Paul Spafford

FileMaker Database Superhero,

Spafford Data Solutions

Tel: 613 838 9956

www.paulspafford.com

www.ottawafilemakerdeveloper.com

www.fmlayoutmode.com

www.fmscripts.com

FileMaker User Group in Ottawa:

www.meetup.com/fudgeo

• ###### 7. Re: Calculation help with related table

Hi Paul,

yeah i no the count is the problem but how do i remove it from the formula.

whenever i click insert on the calculation window it automatically comes up in the formula and is attached to the names. when i try to select it to delete it just gives me other terms to insert like average, max, min, etc. is there a way in bento to manually insert these formulas to i could remove these "count" options?

• ###### 8. Re: Calculation help with related table

Hey Will,

Just had to go into Bento and see how. The problem is that you're trying to do this calculation from a related table. When you do that, Bento assumes that you are wanting to summarize the related data, and it forces that Count (with other options) before each field; it can't be removed.

So what you need to do is create your calc field in the table that actually has the first name and last name fields. Then it isn't an option to use those aggregate functions.

Paul

--

Paul Spafford

FileMaker Database Superhero,

Spafford Data Solutions

Tel: 613 838 9956

www.paulspafford.com

www.ottawafilemakerdeveloper.com

www.fmlayoutmode.com

www.fmscripts.com

FileMaker User Group in Ottawa:

www.meetup.com/fudgeo

• ###### 9. Re: Calculation help with related table

Thanks Paul,

So there is no way to get data from a related table into a new field in a form?

• ###### 10. Re: Calculation help with related table

I don't think there is the way that you're trying to do it. You'd probably have better luck getting an answer from the Bento forum though. Someone on there would know better:

Paul

--

Paul Spafford

FileMaker Database Superhero,

Spafford Data Solutions

Tel: 613 838 9956

www.paulspafford.com

www.ottawafilemakerdeveloper.com

www.fmlayoutmode.com

www.fmscripts.com

FileMaker User Group in Ottawa:

www.meetup.com/fudgeo