Composite Column based on Multiple Criteria

[tamarissa]

I'm looking to create a custom column based on information from 2 other columns. The end result I'm looking for is:

IF Column A (#keep_read) = "Yes" & Column B (#read_status) = "N",
THEN Column C (#send_kobo) = "Yes"
ELSE Column C (#send_kobo) = "No"

I can't figure out how to adapt any of the "If-then-else" formulas for multiple columns . Or maybe there's a better option; as along as it works!

[chaley]

Try this

program:
	strcmp(
		field('#keep_read'), 'yes', 
		'No',
		strcmp(
			field('#read_status'), 'N',
			'No',
			'Yes',
			'No'),
		'No')

This one works too.

program:
	test(
		and(
			strcmp(field('#keep_read'), 'yes', '', 1, ''),
			strcmp(field('#read_status'), 'N', '', 1, '')
		), 
		'Yes',
		'No');

[tamarissa]

Thanks, but I tried both of those and they both resulted in a template error.

[chaley]

Originally Posted by tamarissa

Thanks, but I tried both of those and they both resulted in a template error.

They both work for me.

1. Check that the column names are spelled correctly.
2. Check the parentheses.
3. Don't put any other characters in the template.
4. Ensure that the keyword "program" is at the absolute beginning of the template.
5. Do not intermix {...} templates with "program mode" templates.
6. Post exactly what you tried.

[chaley]

Also, use the "Template Tester" to debug the template. The process goes much faster. Put the tester on the main library context menu (Preferences / Toolbars & menus / The context menu for books in the calibre library.

[tamarissa]

I did get it work, shut everything down and restarted; it seemed to be fine. However, it doesn't do what it says it should; for example, there are multiple books that have the Read Status column = "N" that should say have the new column say "yes", but actually says "no'.

[chaley]

Originally Posted by tamarissa

Where is the template tester? I don't see anywhere in the program, and google that didn't provide any further information.

You get the template tester into the main library context menu in the way I said in my last post.

Also, use the "Template Tester" to debug the template. The process goes much faster. Put the tester on the main library context menu (Preferences / Toolbars & menus / The context menu for books in the calibre library.

Then when you right click on a book, one option presented to you will be the template tester.

[tamarissa]

See Screenshot:

[tamarissa]

There are also books that in the Keep Read column say yes, but it still says no in new column.

[tamarissa]

I've just confirmed this with both options; the only result provided is "No".

[chaley]

Not sure what to say. It works for me.

I used the following template (same as above)

program:
	test(
		and(
			strcmp(field('#keep_read'), 'yes', '', 1, ''),
			strcmp(field('#read_status'), 'N', '', 1, '')
		), 
		'Yes',
		'No');

and these column definitions:






The next screen capture shows the input data and the result. There is exactly one "Y", which is the correct answer (as far as I know).


---

For completeness I also tried it with #keep_read as a text column with the following definition.


The following screen capture shows that the template still works.


---

All I can suggest is that you check that:

• you didn't change the template
• the column definitions are the same as mine
• the data is as you described (the same as mine).

This test template might help you work out where things are going wrong. It shows the result of the two strcmp functions.

program:
	strcat(
		'keep_read strcmp returns "',
		strcmp(field('#keep_read'), 'yes', '', 1, ''),
		'", read_status strcmp returns "',
		strcmp(field('#read_status'), 'N', '', 1, ''),
		'"')

[chaley]

Originally Posted by tamarissa

I did get it work, shut everything down and restarted; it seemed to be fine. However, it doesn't do what it says it should; for example, there are multiple books that have the Read Status column = "N" that should say have the new column say "yes", but actually says "no'.

This post implies that you want the following:

IF Column A (#keep_read) = "Yes" OR Column B (#read_status) = "N",
THEN Column C (#send_kobo) = "Yes"
ELSE Column C (#send_kobo) = "No"

The "&" says that both conditions must be true in order to see "Yes" (the meaning of "and"). This post implies that "Yes" should be displayed if either condition is true (the meaning of "or").

If I am right then you can fix the problem by changing "and(" in the template to "or(".

[tamarissa]

I was able to spend a bunch of time this weekend and managed to follow all your instructions and get everything working properly, so thank you so much. It did need an 'OR', not an 'AND'... I should have thought of that since I run into that issue in Excel sometimes

If I could get one more piece of advice though, I'd really appreciate it... I thought it would be very easy to add one more useful piece of criteria to the code, but it keeps telling me it needs a closing parenthesis, but doesn't say where. As far as I can tell, they are all closed.

program:
	test(
		or(
			strcmp(field('#keep_read'), 'yes', '', 1, ''),
			strcmp(field('#keep_read'), 'series incomplete' '', 1, ''),
			strcmp(field('#read_status'), 'N', '', 1, '')
		), 
		'Yes',
		'No');

Thanks

[chaley]

The problem is one that gets me frequently. The error handling bits of the template parser are not very good , so the parser can get very confused. It isn't really the parens that are unbalanced but is instead a missing comma.

The corrected template is below with missing comma is in large font bold.

program:
	test(
		or(
			strcmp(field('#keep_read'), 'yes', '', 1, ''),
			strcmp(field('#keep_read'), 'series incomplete', '', 1, ''),
			strcmp(field('#read_status'), 'N', '', 1, '')
		), 
		'Yes',
		'No');

Glad you got it working.