#case #netsuite
#дело #нетсуит
Вопрос:
Я пытаюсь построить сложный случай, когда оператор для сохраненного поиска в NetSuite.
Это «ДЛИННАЯ» версия, но, очевидно, в ней слишком много символов, и я знаю, что ее можно упростить, но я не уверен, как это сделать.
CASE WHEN {status} = 'Cancelled' THEN 0 WHEN {subsidiary} = 'Ciatti Australia' AND {status} = 'Billed' AND {applyingtransaction.custbody_shared_comm_type} = 'Ciatti Internal - 50/50 share between Selling/Buying office' THEN {applyingtransaction.fxamount}/2 WHEN {subsidiary} = 'Ciatti Australia' AND {status} = 'Billed' AND {applyingtransaction.custbody_shared_comm_type} = 'Ciatti/iDeal - 1/3 share between Selling office, iDeal amp; Mr Li etc. (2/3 to iDeal)' THEN {applyingtransaction.fxamount}/3 WHEN {subsidiary} = 'Ciatti Australia' AND {status} = 'Billed' AND {applyingtransaction.custbody_shared_comm_type} = 'Ciatti/iDeal - Existing Supplier/Existing Buyer - 1/3 to iDeal' THEN {applyingtransaction.fxamount}/3*2 WHEN {subsidiary} = 'Ciatti Australia' AND {status} = 'Billed' AND {applyingtransaction.custbody_shared_comm_type} = 'Ciatti/iDeal - Existing Supplier/New Buyer - 50/50 share between Selling office and iDeal' THEN {applyingtransaction.fxamount}/2 WHEN {subsidiary} = 'Ciatti Australia' AND {status} = 'Billed' AND {applyingtransaction.custbody_shared_comm_type} = 'Ciatti/iDeal - New Suppler/Existing Buyer - 1/3 share between Selling office, AU office and iDeal' THEN {applyingtransaction.fxamount}/3*2 WHEN {subsidiary} = 'Ciatti Australia' AND {status} = 'Billed' AND {applyingtransaction.custbody_shared_comm_type} = 'Ciatti/iDeal - New Supplier/New Buyer - 50/50 share between Selling office and iDeal' THEN {applyingtransaction.fxamount}/2 WHEN {subsidiary} = 'Ciatti Australia' AND {status} = 'Billed' AND {applyingtransaction.custbody_shared_comm_type} = 'Not to be shared' THEN {applyingtransaction.fxamount} WHEN {subsidiary} = 'Ciatti Australia' AND {custbody_shared_comm_type} = 'Ciatti Internal - 50/50 share between Selling/Buying office' THEN {fxamount}/2 WHEN {subsidiary} = 'Ciatti Australia' AND {custbody_shared_comm_type} = 'Ciatti/iDeal - 1/3 share between Selling office, iDeal amp; Mr Li etc. (2/3 to iDeal)' THEN {fxamount}/3 WHEN {subsidiary} = 'Ciatti Australia' AND {custbody_shared_comm_type} = 'Ciatti/iDeal - Existing Supplier/Existing Buyer - 1/3 to iDeal' THEN {fxamount}/3 WHEN {subsidiary} = 'Ciatti Australia' AND {custbody_shared_comm_type} = 'Ciatti/iDeal - Existing Supplier/New Buyer - 50/50 share between Selling office and iDeal' THEN {fxamount}/2 WHEN {subsidiary} = 'Ciatti Australia' AND {custbody_shared_comm_type} = 'Ciatti/iDeal - New Suppler/Existing Buyer - 1/3 share between Selling office, AU office and iDeal' THEN {fxamount}/3 WHEN {subsidiary} = 'Ciatti Australia' AND {custbody_shared_comm_type} = 'Ciatti/iDeal - New Supplier/New Buyer - 50/50 share between Selling office and iDeal' THEN {fxamount}/2 WHEN {subsidiary} = 'Ciatti Australia' AND {custbody_shared_comm_type} = 'Not to be shared' THEN {fxamount}/2 __
Затем то же самое, что и выше, когда Дочерняя компания НЕ является «Ciatti Australia».
Ответ №1:
Один из способов уменьшить формулу-использовать внутренний идентификатор поля вместо текстового значения. Это можно сделать, добавив «.id» в поле, т. е.
{custbody_shared_comm_type} Для
{custbody_shared_comm_type.id} и дело изменится с
WHEN {subsidiary.id} = 1 AND {custbody_shared_comm_type} = 'Ciatti/iDeal - 1/3 share between Selling office, iDeal amp; Mr Li etc. (2/3 to iDeal)' THEN {fxamount}/3 Для
WHEN {subsidiary.id} = 1 AND {custbody_shared_comm_type.id} = 4 THEN {fxamount}/3 Другой способ-создать встроенные предложения case, чтобы исключить повторение таких строк, как
КОГДА {дочерняя компания} = «Ciatti Австралия» И {статус} = «Выставлен счет»
т.е.
CASE WHEN {status} = 'Cancelled' THEN 0 WHEN {subsidiary} = 'Ciatti Australia' AND {status} = 'Billed' THEN CASE WHEN {applyingtransaction.custbody_shared_comm_type} = 'Ciatti Internal - 50/50 share between Selling/Buying office' THEN {applyingtransaction.fxamount}/2 CASE WHEN {applyingtransaction.custbody_shared_comm_type} = 'Ciatti/iDeal - 1/3 share between Selling office, iDeal amp; Mr Li etc. (2/3 to iDeal)' THEN {applyingtransaction.fxamount}/3 CASE WHEN {applyingtransaction.custbody_shared_comm_type} = 'Ciatti/iDeal - Existing Supplier/Existing Buyer - 1/3 to iDeal' THEN {applyingtransaction.fxamount}/3*2 . . . ELSE 0 END WHEN {subsidiary} = 'Ciatti Australia' AND NOT {status} = 'Billed' THEN . . . WHEN NOT {subsidiary} = 'Ciatti Australia' AND {status} = 'Billed' THEN CASE WHEN ... CASE WHEN ... CASE WHEN ... . . . ELSE 0 END WHEN NOT {subsidiary} = 'Ciatti Australia' AND NOT {status} = 'Billed' THEN CASE WHEN ... CASE WHEN ... CASE WHEN ... . . . ELSE 0 END Когда они будут объединены, результат будет примерно таким :
CASE WHEN {status} = 'Cancelled' THEN 0 WHEN {subsidiary.id} = 1 AND {status} = 'Billed' THEN CASE WHEN {applyingtransaction.custbody_shared_comm_type.id} = 1 THEN {applyingtransaction.fxamount}/2 CASE WHEN {applyingtransaction.custbody_shared_comm_type.id} = 2 THEN {applyingtransaction.fxamount}/3 CASE WHEN {applyingtransaction.custbody_shared_comm_type.id} = 3 THEN {applyingtransaction.fxamount}/3*2 . . . ELSE 0 END WHEN {subsidiary.id} = 1 AND NOT {status} = 'Billed' THEN . . . WHEN NOT {subsidiary.id} = 1 AND {status} = 'Billed' THEN CASE WHEN ... CASE WHEN ... CASE WHEN ... . . . ELSE 0 END WHEN NOT {subsidiary.id} = 1 AND NOT {status} = 'Billed' THEN CASE WHEN ... CASE WHEN ... CASE WHEN ... . . . ELSE 0 END Очевидно, замена фактических идентификаторов дочерних компаний и правильных идентификаторов для строковых представлений в ваших примерах.