使用 Microsoft Flow 筛选和复制数据Filter and copy data with Microsoft Flow

本演练介绍如何创建一个流,用于监视源中新建或更改的项,然后将这些更改复制到目标。This walkthrough shows you how to create a flow that monitors a source for new or changed items and then copies those changes to a destination. 如果用户在一个位置输入数据,但团队需要在另一个位置或以另一种格式获得该数据,则可以创建此类流。You may create a flow like this one if your users enter data in one location, but your team needs it in a different location or format.

尽管本演练将数据从 Microsoft SharePoint 列表(源)复制到 Azure SQL 数据库表(目标),但在 Microsoft Flow 支持的超过 150 种服务之间均可以复制数据。While this walkthrough copies data from a Microsoft SharePoint list (the source) to an Azure SQL Database table (the destination), you can copy data among any of the more than 150 services that Microsoft Flow supports.

重要

在目标中所做的更改不会复制到源,因为不支持双向同步。Changes you make in the destination aren't copied to the source because two-way syncs aren't supported. 如果尝试设置双向同步,会创建一个无限循环,在源和目标之间无休止地发送更改。If you attempt to set up a two-way sync, you'll create an infinite loop where changes are sent endlessly between the source and destination.

先决条件Prerequisites

  • 对数据源和目标的访问权限。Access to a data source and a destination. 本演练不包括创建源和目标的步骤。This walkthrough doesn’t include steps to create the source and destination.
  • 有权访问 Microsoft FlowAccess to Microsoft Flow.
  • 大致了解如何存储数据。A basic understanding of how your data is stored.
  • 熟悉创建流的基础知识。Familiarity with the basics of creating flows. 可以查看如何添加操作、触发器条件You can review how to add actions, triggers, and conditions. 以下步骤假设用户了解如何执行这些操作。The following steps assume that you know how to perform these actions.

提示

并非源和目标中的每个列名称都需要匹配,但必须在插入或更新项时为所有必需的列提供数据。Every column name in the source and destination don't need to match, but you must provide data for all required columns when you insert or update an item. Microsoft Flow 标识所需的字段。Microsoft Flow identifies the required fields for you.

步骤的简要概述Quick overview of the steps

如果熟悉 Microsoft Flow,可使用以下快速步骤将数据从数据源复制到另一个数据源:If you're comfortable with Microsoft Flow, use these quick steps to copy data from one data source to another:

  1. 标识要监视的源和要将更改的数据复制到的目标。Identify the source you'll monitor and the destination to which you'll copy changed data. 确认拥有两者的访问权限。Confirm you've access to both.
  2. 标识至少一个可唯一标识源和目标中的项的列。Identify at least one column that uniquely identifies items in the source and destination. 在以下示例中,我们使用“标题”列,但也可以使用任何想要的列。In the example that follows, we use the Title column, but you could use any column(s) you want.
  3. 设置用于监视源中的更改的触发器。Set up a trigger that monitors the source for changes.
  4. 搜索目标以确定是否存在已更改的项。Search the destination to determine if the changed item exists.
  5. 按如下方式使用条件Use a Condition like this:
    • 如果目标中不存在新建或更改的项,则创建它。If the new or changed item doesn't exist in the destination, create it.
    • 如果目标中存在新建或更改的项,则更新它。If the new or changed item exists in the destination, update it.
  6. 触发流,然后确认新建或更改的项正在从源复制到目标。Trigger your flow, and then confirm that new or changed items are being copied from the source to the destination.

备注

如果之前未创建与 SharePoint 或 Azure SQL 数据库的连接,请在收到登录提示时按照说明进行操作。If you haven't created a connection to SharePoint or Azure SQL Database previously, follow the instructions when you're prompted to sign in.

下面是创建该流的详细步骤。Here are the detailed steps to create the flow.

监视源中的更改Monitor the source for changes

  1. 登录到 Microsoft Flow,依次选择“我的流” > “从空白创建”。Sign into Microsoft Flow, select My flows > Create from blank.

  2. 搜索 SharePoint,然后从触发器列表中选择“SharePoint - 当创建或修改项时”。Search for SharePoint > select the SharePoint - When an item is created or modified trigger from the list of triggers.

  3. 输入站点地址,然后选择“当创建或修改项时”卡上的“列表名称”。Enter the Site Address and then select the List Name on the When an item is created or modified card.

    为流监视新建或更新项的 SharePoint 列表提供站点地址列表名称Provide the Site Address and List Name for the SharePoint list your flow monitors for new or updated items.

    配置 sharepoint 触发器

搜索目标中新建或更改的项Search the destination for the new or changed item

我们使用 SQL Server - 获取行操作搜索目标中新建或更改的项。We use the SQL Server - Get rows action to search the destination for the new or changed item.

  1. 选择“新建步骤” > “添加操作”。Select New step > Add an action.

  2. 搜索获取行,选择“SQL Server - 获取行”,然后从“表名称”列表中选择要监视的表。Search for Get rows, select SQL Server - Get rows, and then select the table you want to monitor from the Table name list.

  3. 选择“显示高级选项”。Select Show advanced options.

  4. 在“筛选器查询”框中,输入 Title eq ',从动态内容列表中选择“标题”令牌,然后输入 'In the Filter Query box, enter Title eq ', select the Title token from the dynamic content list, and then enter '.

    上一步假设要将源和目标中的行标题匹配。The previous step assumes you're matching the Titles of the rows in the source and the destination.

    “获取行”卡的外观现在应如此图片所示:The Get rows card should now look like this image:

    尝试从目标数据库中获取项

检查是否找到了新建或更改的项Check if the new or changed item was found

依次选择“新步骤” > “添加条件”来打开“条件”卡。Select New step > Add a condition to open the Condition card.

在“条件”卡上:On the condition card:

  1. 选中左侧的框。Select the box on the left.

    此时会打开“从此流中使用的应用和连接器添加动态内容”列表。The Add dynamic content from the apps and connectors used in this flow list opens.

  2. 从“获取行”类别中选择“值”。Select value from the Get rows category.

    提示

    确认已从“获取行”类别中选择“值”。Confirm you've selected value from the Get rows category. 不要从“当创建或修改项时”类别中选择“值”。Don't select value from the When an item is created or modified category.

  3. 从中央框中的列表中选择“等于”。Select is equal to from the list in the center box.

  4. 在右侧的框中输入 0(零)。Enter 0 (zero) in the box on the right side.

    “条件”卡现在类似于下图:The Condition card now resembles this image:

    配置条件

  5. 选择“在高级模式中进行编辑”。Select Edit in advanced mode.

    高级模式打开时,你可在框中看到 @equals(body('Get_rows')?['value'], 0) 表达式。When advanced mode opens, you see @equals(body('Get_rows')?['value'], 0) expression in the box. 通过围绕 body('Get_items')?['value'] 函数添加 length() 来编辑此表达式。Edit this expression by adding length() around the body('Get_items')?['value'] function. 整个表达式现在显示如下:@equals(length(body('Get_rows')?['value']), 0)The entire expression now appears like this: @equals(length(body('Get_rows')?['value']), 0)

    “条件”卡现在类似于下图:The Condition card now resembles this image:

    配置条件

    提示

    添加 length() 函数可使流检查“值”列表,并确定它是否包含任何项。Adding the length() function allows the flow to check the value list and determine if it contains any items.

当流从目标中“获取”项时,有两种可能的结果。When your flow "gets" items from the destination, there're two possible outcomes.

结果Outcome 下一步Next step
项存在The item exists 更新该项Update the item
项不存在The item doesn't exist 创建新项Create a new item

备注

下面显示的“插入行”和“更新行”卡的图像可能与实际外观有所不同,因为这些卡显示在流中使用的 Azure SQL 数据库表中的列名称。The images of the Insert row and Update row cards shown next may differ from yours because these cards show the names of the columns in the Azure SQL Database table that's being used in the flow.

在目标中创建该项Create the item in the destination

如果目标中不存在该项,则使用 SQL Server - 插入行操作创建它。If the item doesn't exist in the destination, create it using the SQL Server - Insert row action.

在“条件”的“如果是”分支上:On the If yes branch of the Condition:

  1. 选择“添加操作”,搜索“插入行”,然后选择“SQL Server - 插入行”。Select Add an action, search for insert row, and then select SQL Server - Insert row.

    将打开“插入行”卡。The Insert row card opens.

  2. 从“表名称”列表中,选择将插入新建项的表。From the Table name list, select the table into which the new item will be inserted.

    “插入行”卡将展开并显示选定表中的所有字段。The Insert row card expands and displays all fields in the selected table. 带有星号 (*) 的字段是必填字段,必须填充才能使行生效。Fields with an asterisk (*) are required and must be populated for the row to be valid.

  3. 选择要填充的每个字段,并输入数据。Select each field that you want to populate and enter the data.

    可以手动输入数据,从“动态内容”中选择一个或多个令牌,或者在字段中输入文本和令牌的任意组合。You may enter the data manually, select one or more tokens from the Dynamic content, or enter any combination of text and tokens into the fields.

    “插入行”卡现在类似于下图:The Insert row card now resembles this image:

    配置条件

在目标中更新该项Update the item in the destination

如果目标中存在该项,则更新其更改。If the item exists in the destination, update it with the changes.

  1. SQL Server - 更新行操作添加到“条件”的“如果否”分支。Add the SQL Server - Update row action to the If no branch of the Condition.

  2. 按照此文档的创建项部分中的步骤填充表中的字段。Follow the steps in the create the item section of this document to populate the fields of the table.

    查看环境

  3. 在页面顶部,在“流名称”框中输入流名称,然后选择“创建流”来保存它。At the top of the page, enter a name for your flow in the Flow name box, and then select Create flow to save it.

    为流命名

现在,每当 SharePoint 列表(源)中的项发生更改时,流都会触发,并在 Azure SQL 数据库(目标)中插入新项或更新现有项。Now, whenever an item in your SharePoint list (source) changes, your flow triggers and either inserts a new item or updates an existing item in your Azure SQL Database (destination).

备注

当从源中删除项时,不会触发流。Your flow isn't triggered when an item is deleted from the source. 如果这是一个重要方案,请考虑添加一个单独的列,用于指示何时不再需要项。If this is an important scenario, consider adding a separate column that indicates when an item is no longer needed.

了解详情Learn more

在流中使用数据操作Use data operations in your flows.