Custom Column with division

[Metaxil]

Alright, this may be an easy question to answer but since my program experience is if/then statements and some initial basic that was self=taught on tandy's a *long* time ago, I'm just knowledgeable enough to be dangerous as they say. However, for this issue, I can't get it quite how I'd like it. I've been screwing around with this for about an hour trying to get it how I'd like, and while I got it partially to work the way I want it, I can live with it if necessary, but I'd prefer to get it done as I want it.

My problem. I have two columns, "#chapters" and "#reviews" that I want to use to create a custom column. This new custom column is a simple division of review/chapters. I'd like to have a value (with one decimal) or no value at all (not 0).

Initially I just used a simple divide function:
divide(field('#reviews'), field('#chapters'))

However, because this database has a lot of stories in it, some with only chapters, some with no chapters either, I was getting an error code. In my mind this is unacceptable, so I used the CMP function (don't know if that was the right way to go) to insure everything had a value. I'd did this because there's an example of how to do it with something else on the board, and I was able to understand how the function worked through the example. It's probably not the best way to do it, since I want a null value if "#reviews" is not available. I'm guessing I have to use "ifempty" somehow, but I'm not 100% sure how to write it out.

Currently I have this:
format_number(
reviews_val=cmp(field("#reviews"),1,1, field("#reviews"), field("#reviews"));
chapter_val=cmp(field("#chapters"),1,1, field("#chapters"), field("#chapters"));
divide(reviews_val,chapter_val),"{0:5.1f}")

So everything has a value, some of them are small (0.0) or (0.1) because there's no actual 'reviews' to divide by, even if there's chapters. What I'd like is a formula that basically leaves the column blank if there's no review value or no chapter value. I'm not 100% sure this is possible, but it likely is.

So, anybody out there willing to help me out?

[eschwartz]

Try this. Sorry it's a little bulky, I have tried to comment the more obtuse bits.

program:

# if either is empty, return zero for reviews
r = test(
	field('#chapters'),
	ifempty(
		field('#reviews'),
		'0'
	),
	'0'
);
# don't divide by zero.
c = ifempty(
	field('#chapters'),
	'1'
);

ans = divide(r,c); 

cmp(
	ans,
	0,
# if zero return blank
	'',
	'',
	ans
)

[Metaxil]

That worked (with a modification and a fix). You forgot to put #review as '#review' (easily fixed) and I added a format_number at the beginning so I didn't have a 6 decimal answer.
The modified formula for anyone else interested

program:format_number(
r = test(field('#chapters'),ifempty(field('#reviews'),'0'),'0');
c = ifempty(field('#chapters'),'1');ans = divide(r,c); 
cmp(ans,0,'','',ans),"{0:5.1f}")

I removed the comments, as they are explained above, for anyone interested. eschwartz, I appreciate your help.

[eschwartz]

hehe, sorry about that.

Glad it worked though.

format_number should be at the end though, enclosing cmp.

[chaley]

Originally Posted by Metaxil

That worked (with a modification and a fix). You forgot to put #review as '#review' (easily fixed) and I added a format_number at the beginning so I didn't have a 6 decimal answer.
The modified formula for anyone else interested

program:format_number(
r = test(field('#chapters'),ifempty(field('#reviews'),'0'),'0');
c = ifempty(field('#chapters'),'1');ans = divide(r,c); 
cmp(ans,0,'','',ans),"{0:5.1f}")

I removed the comments, as they are explained above, for anyone interested. eschwartz, I appreciate your help.

I am astounded that this template works with assignment statements as parameters for the format_number function. The parser isn't supposed to let that through. I won't fix the "bug" though.

As the language was designed and as eschwartz said, your template should be

program:
r = test(field('#chapters'),ifempty(field('#reviews'),'0'),'0');
c = ifempty(field('#chapters'),'1');ans = divide(r,c); 
format_number(cmp(ans,0,'','',ans),"{0:5.1f}")

[Metaxil]

Alright. As I said in the first post. Dangerous. I have just enough knowledge to macgyver it most of the time, with trial and error testing methods. Plus I'm so used to working with nested statements, that's typically what I do. I actually was wondering if that was going to work the first time I tried it, but since it did, I didn't have to change anything, so that was good enough for me... and probably would have been until it broke. Thanks for fixing the code, I've changed it now.

I appreciate both your help with this.