一、简介

  1. UPSERT公式用于往目标表单中插入或者更新数据

  2. UPSERT更新目标有两种：

    1. 表单中的普通组件值(比如单行多行等数据)

    2. 表单下明细值，明细下有多条数据，可以根据设置的条件，只更新符合条件的明细数据。

  3. 一些概念

    1. 主条件：一个表单下有多条数据，需要我们设置条件，只更新符合条件的表单数据，这个条件称为主条件，用于定位哪条数据。

    2. 子条件：先用主条件定位到具体哪条表单数据需要更新。但是如果要更新的是这条数据下某个明细的某些符合条件的数据，这时就还需要另外一个条件，专门用来定位明细下哪些数据要更新，这个条件称为子条件。主条件定位哪条表单数据，子条件定位更新的表单数据下某个明细下哪条明细数据要更新。

    3. 录入表：配置高级公式，录入数据，触发高级公式的表单。

    4. 目标表：被高级公式修改/插入数据的表单。

  4. UPSERT格式 : UPSERT(目标表, 主条件, 子条件, 目标列1, 目标值1, 目标列2, 目标值2…)

    1. 目标表: 要插入/更新数据的目标表单。

    2. 主条件: 定位目标表下需要更新的表单数据。主条件是由逻辑函数构成。比如EQ(存货表.商品名, “铅笔”)， 则表示目标表是“存货表”, 要更新的数据是：商品名为“铅笔”的这批数据。如果有多个条件，可以用AND函数组合。比如AND(EQ(存货表.商品名, “铅笔”), GT(存货表.存货量 , 10)) 表示要更新的数据是：商品名为“铅笔”，并且“存货量”大于10的那批数据。

    3. 子条件: 要更新的表单数据已经确定，但要更新的是这条数据下某个明细的明细数据。用子条件定位明细哪些数据要更新。子条件是由逻辑函数构造。如果不需要更新明细下的值，则填””，不能不填。

    4. 目标列：

      1. 如果子条件为””，表示不更新明细下的值，那么目标列必须为目标表下的普通组件。

      2. 如果子条件不为””，表示更新的是明细下的值。那么目标列必须为要更新的目标表要更新的明细下的组件。

    5. 目标值: 指目标列要更新成的值。目标值可以由当前录入表某个组件值，要更新目标表某个组件值，固定值通过其他函数组合而成。

  5. UPSERT例子
    接下来，会按照以下几个场景介绍UPSERT函数的使用

    1. 录入表的普通组件数据插入/更新目标表的普通组件数据

    2. 录入表的明细组件数据插入/更新目标表的普通组件数据

    3. 录入表的普通组件数据插入/更新到目标表的明细组件数据

二、录入表普通组件数据插入/更新目标表的普通组件数据

  1. 使用的表单：

    1. 进货表:商品名、进货量

    2. 存货表:商品名、存货量

  2. 需求：进货表中录入一个商品，如果存货表中存在相同商品名的数据，则在该商品的存货量的基础上加上进货量。如果存货表中查找不到该商品名，则往存货表中插入该商品名，存货量为进货量。

  3. 在进货表的业务关联规则中，配置公式：UPSERT(存货表,EQ(存货表.商品名,商品名),””,存货表.商品名,商品名,存货表.存货量,存货表.存货量+进货量)
    UPSERT - 图1

  1. 公式解析

    1. 目标表：存货表。

    2. 主条件：EQ(存货表.商品名, 商品名)。更新的数据是存货表中商品名为录入表录入数据的商品名。

    3. 子条件：由于更新的是“存货表”中的普通组件：商品名和存货量，不涉及明细，因此子条件填写为””

    4. 目标列：存货表.商品名。

    5. 目标值：商品名。 如果查找不到数据，在插入数据时，存货表的商品名值为进货表的商品名值。

    6. 目标列：存货表.存货量

    7. 目标值：存货表.存货量 + 进货量。 根据条件匹配到存货商品后，在原来存货量的基础上加上这次的进货量。

  2. 完整配置过程

UPSERT - 图2

三、录入表明细组件数据插入/更新目标表的普通组件数据

  1. 使用的表单：

    1. 进货表: 商品类别(下拉单选)、进货明细(明细组件)。进货明细下有：商品名(单行输入框)、进货量(数字输入框)。

    2. 存货表:商品类别(下拉单选)、商品名(单行输入框)、存货量(数字输入框)

  2. 需求：进货表下有进货明细，一次可以录入多个商品。对于录入的多个商品，在存货表中假如查找不到对应商品(商品名和商品类别都相同时，表示商品相同)，则插入数据。如果能找到对应商品，则更新对应商品的存货量(存货量=原来的存货量+进货量)。

  3. 在进货表的业务关联规则中，配置公式：
    UPSERT - 图3

  1. 公式解析:

    1. 目标表：存货表。

    2. 主条件：AND(EQ(存货表.商品名,明细.商品名),EQ(存货表.商品类别,商品类别)).定位数据用到两个条件，即商品名相等(EQ(存货表.商品名,明细.商品名))和商品类别相等(EQ(存货表.商品类别,商品类别))。两个条件要同时成立，则用AND将两个条件放一起。

    3. 子条件：由于更新的是“存货表”中的普通组件：商品名、商品类别和存货量，没有更新明细，因此子条件填写为””.

    4. 目标列：存货表.商品名。

    5. 目标值：商品名。 如果查找不到数据，在插入数据时，存货表的商品名值为进货表的商品名值。

    6. 目标列：存货表.存货量

    7. 目标值：存货表.存货量 + 进货量。 根据条件匹配到存货商品后，在原来存货量的基础上加上这次的进货量。

    8. 目标列：存货表.商品类别。

    9. 目标值：商品类别。 如果查找不到数据，在插入数据时，存货表的商品类别值为进货表的商品类别。

  1. 操作的完整过程和效果如下图

    1. 一开始录入“铅笔”和“橡皮”，类别为文具，数量10。由于存货表中没对应数据，直接插入两条数据。

    2. 第二次录入“铅笔”和“铅笔盒”，类别为文具，数量都为10。由于存货表中类别为文具，商品名为铅笔的已经有了，所以更新，存货量由原来的10，变为20。铅笔盒没有，插入数据。

    3. 第三次录入“铅笔”，但类别选择了“其他”。存货表中只有类别为文具的铅笔，不符合主条件。因此当做不存在，插入数据。
      UPSERT - 图4

三、录入表普通组件数据插入/更新目标表的明细组件数据

  1. 使用的表单：

    1. 风险类型: 类型(单行输入框)、事件明细(明细组件)，事件明细下有事件名称(单行输入框)。

    2. 风险事件: 风险类型(下拉单选，数据来源于风险类型表中的类型数据)、事件名称(单行输入框)
      UPSERT - 图5
      UPSERT - 图6

  1. 需求：“风险类型”表单，维护着风险类型和该类型下有哪些风险事件的数据。“风险事件”表单记录的是每个风险事件的详情。

    1. 当我们在“风险类型”表单中的事件明细下新增一个风险事件时，“风险事件”表单中也会相应增加一条数据。

    2. 当我们在“风险事件”表单中新增一个风险事件时，相应的风险类型的事件明细下就要新增一条事件记录。
      即实现单表数据聚合到一个明细下，和明细数据拆分到单表里的双向过程。

  2. 公式配置详情

    1. 针对(2.i)的需求，相当于明细数据插入到普通数据的配置逻辑，公式为：UPSERT - 图7

    2. 公式解析：

      1. 目标表：风险事件

      2. 主条件：AND(EQ(风险事件.风险类型,风险类型),EQ(风险事件.事件名称,事件明细.事件名称))。匹配风险事件表数据需要两个条件：风险类型相同(EQ(风险事件.风险类型,风险类型))和事件名称相同(EQ(风险事件.事件名称,事件明细.事件名称)).两个条件需要同时成立，用AND连接。

      3. 子条件：更新的是风险事件下的两个普通组件，不涉及明细，不需要子条件，为””.

      4. 目标列：风险类型

      5. 目标值：风险类型

      6. 目标列：风险事件.事件名称

      7. 目标值：事件明细.事件名称。 明细下有多个事件名称数据，则往“风险事件”表中插入多条数据

  1. 针对(2.ii)的需求，相当于普通数据插入到明细数据的配置逻辑，公式为：UPSERT - 图8

  1. 公式配置解析

    1. 目标表：风险类型

    2. 主条件：EQ(风险类型.风险类型,风险类型)。用“风险类型”输入框的值就能唯一定位“风险类型”表的某条数据。主条件用于定位哪条数据需要更新，因此不能使用目标表中的明细组件。

    3. 子条件：EQ(风险类型.事件明细.事件名称,事件名称)。 这次是要往“风险类型”表中的“事件明细”组件中插入数据。因此需要子条件。是否需要插入明细数据的条件是明细下是否已经有相同的风险事件存在。因此用EQ(风险类型.事件明细.事件名称,事件名称)来匹配明细下的风险事件。子条件定位明细下哪条数据需要更新，因此必须使用到要更新的明细下的组件。

    4. 目标列：风险类型.事件明细.事件名称。 由于要操作的是明细数据，因此目标列也只能是明细下的某个组件。这次选的是明细下的“事件”组件。

    5. 目标值：事件名称。

  2. 完整配置过程和效果如下

    1. 风险类型表单中配置“业务关联规则”，设置数据提交时，往“风险事件”表中录入对应的风险事件数据。

    2. 风险事件表单中，配置“风险类型”下拉选择数据来自于“风险类型”表单中的类型数据，确保风险类型是存在的。

    3. 风险事件表单中，配置“业务关联规则”，设置风险事件表单提交数据时，往“风险类型”表单的明细中录入对应的事件数据。

    4. “风险类型”表单中录入“类型A”，事件明细下有“事件A-1”，”事件A-2”两种风险事件。对应的，“风险事件”表单下有对应的两条数据插入。

    5. “风险事件”表单中录入“类型A”，“事件A-10”的数据，查看风险类型表中“类型A”的明细数据，可以发现插入了一条“事件A-10”的数据。

UPSERT - 图9

其他注意点

  1. 不能既更新普通组件，又要更新明细下的值。如果有这种场景，请分成两个UPSERT函数，一个用来更新普通组件，一个用来更新明细下的数据。

  2. UPSERT往目标表明细中录入数据时，前提是能通过主条件定位到某条表单数据。如果定位不到，则不进行任何操作。针对这种场景，可以配置两个业务关联规则。第一个业务关联规则配置定位表单数据，不存在插入。第二个业务关联规则配置往表单数据的明细下插入数据，从而确保主条件肯定能定位到数据。

  3. 在上面讲解的商品进货存货的场景中，其实当表单数据删除时，是需要把存货量扣除的，此时可以在“单据删除”上，配置UPDATE函数，用于扣除数据，从而保证数据是一致的。

  4. UPSERT函数一次最多只能更新50条表单数据，超出会报错导致提交失败。一次最多只能插入更新某条表单数据下50个明细数据，超出也会报错提示提交失败。

  5. UPSERT函数一次只能只能操作表单下一个明细组件的数据。如果有操作多个明细组件数据的需求，请配置多个UPSERT函数。

  6. 主条件和子条件是由逻辑函数构造的。除了AND和OR之外，其他逻辑函数第一个参数必须是目标表中的组件，两个参数的位置不能调换。