phpcms 表单数据表_工作表数据输入表单警报

phpcms 表单数据表

One of my Excel sample files has a data entry sheet, with buttons to add/update database records that are stored on a different sheet. I've just added a new version of the file, that helps you remember to save your changes.

我的一个Excel示例文件有一个数据输入表，带有用于添加/更新存储在不同表上的数据库记录的按钮 。 我刚刚添加了该文件的新版本，可以帮助您记住要保存的更改。

您将如何做？ (How Would You Do This?)

Someone uses my data entry form in their workplace, and asked how to remind people to save their changes.

有人在工作场所使用我的数据输入表，并询问如何提醒人们保存更改。

Apparently, people enter new records, or change existing records, and then wander off (to a different sheet or record). They don't remember to click the Update or Add buttons!

显然，人们输入新记录或更改现有记录，然后徘徊(到另一个工作表或记录)。 他们不记得单击“更新”或“添加”按钮！

That's an interesting problem. How would you get people to save their changes in this data entry form?

那是一个有趣的问题。 您将如何让人们将他们的更改保存在此数据输入表单中？

使用宏？ (Use a Macro?)

My first thought was to use a macro. Excel can run code when you do specific things (events), such as moving to a different sheet, or closing a file.

我的第一个想法是使用宏。 当您执行特定的操作(事件)(例如，移至另一张工作表或关闭文件)时，Excel可以运行代码。

Unfortunately, people are unpredictable, and we'd have to set up lots of event code, to catch all the ways they might wander away.

不幸的是，人们是无法预测的，我们必须设置许多事件代码，以捕捉他们可能流连忘返的所有方式。

All that code could slow down the workbook, and that would annoy everyone who uses the data entry form.

所有这些代码可能会减慢工作簿的速度，并且会激怒使用数据输入表单的每个人。

没有宏的警报 (Alerts Without Macros)

Instead of using macros, I came up with a solution that uses a few formulas and a bit of conditional formatting. It won't have much impact on the workbook performance, and will help the people who need reminders.

我没有使用宏，而是想出了一个使用一些公式和一些条件格式的解决方案。 它不会对工作簿的性能产生太大影响，并且会帮助需要提醒的人员。

For example, if someone makes changes to an existing record, the label turns orange, for each updated field. That's done with conditional formatting.

例如，如果某人对现有记录进行了更改，则对于每个更新的字段，标签将变为橙色。 这是通过条件格式完成的。

Also, below the form's buttons, a formula shows a reminder message, with a bright yellow background – "Click Update, to save your changes!"

另外，在表单按钮下方，公式将显示一条提醒消息，并带有明亮的黄色背景–“单击更新，以保存您的更改！”

这个怎么运作 (How It Works)

First, at the top of the Input sheet, a formula checks the Order ID, to see if it's already in the database (stored on the PartsData sheet).

首先，在“输入”表的顶部，一个公式检查“订单ID”，以查看它是否已经存在于数据库中(存储在PartsData表中)。

=AND(OrderID<>"", COUNTIF(PartsData!C:C, OrderID)>0)

= AND(OrderID <>“”，COUNTIF(PartsData！C：C，OrderID)> 0)

The result will be TRUE or FALSE, and that cell is named CheckID – we'll refer to it in other formulas.

结果将为TRUE或FALSE，并且该单元格名为CheckID-我们将在其他公式中对其进行引用。

获取现有数据 (Get the Existing Data)

Off to the right on the data entry sheet, there are INDEX/MATCH formulas, to pull the record's current data from the database.

数据输入表右侧的INDEX / MATCH公式 ，用于从数据库中提取记录的当前数据。

Here is the formula in cell G5:

这是单元格G5中的公式：

=IF(CheckID=FALSE,"NEW", INDEX(Table1, MATCH(OrderID, Table1[Order ID],0), MATCH(Input!P6, Table1[#Headers],0)))

= IF(CheckID = FALSE，“ NEW”，INDEX(Table1，MATCH(OrderID，Table1 [Order ID]，0)，MATCH(Input！P6，Table1 [#Headers]，0)))

INDEX / MATCH公式 (The INDEX/MATCH Formula)

That formula starts by checking the CheckID cell. If it's FALSE, the record is not in the database, so the result will be "NEW".

该公式从检查CheckID单元格开始。 如果为FALSE，则记录不在数据库中，因此结果将为“ NEW”。

=IF(CheckID=FALSE,"NEW",

= IF(CheckID = FALSE，“ NEW”，

Otherwise, the INDEX function returns a value from Table1, which stores the records on the PartsData sheet

否则，INDEX函数从Table1返回一个值，该值将记录存储在PartsData表上

INDEX(Table1,

索引(表1，

To find the correct row in Table1, a MATCH formula looks for the Order ID in the Order ID column.

为了在表1中找到正确的行，MATCH公式在“订单ID”列中查找“订单ID”。

MATCH(OrderID, Table1[Order ID],0),

MATCH(OrderID，Table1 [Order ID]，0)，

To find the correct column, a MATCH formula looks for the label name in the Table1 headers.

为了找到正确的列，MATCH公式在Table1标头中查找标签名称。

MATCH(Input!P6, Table1[#Headers],0)))

匹配(输入！P6，表1 [#Headers]，0)))

So, for Order ID 10102, the Part is "Spring"

因此，对于订单ID 10102，零件为“弹簧”

检查差异 (Check for Differences)

In column R on the Input sheet, a formula compares the database value (from column Q), to the value in the Input area (in column D)

在“输入”表的R列中，一个公式将数据库值(来自Q列)与“输入”区域中的值(在D列中)进行比较

=IF(CheckID=FALSE,0,--(Q5<>D5))

= IF(CheckID = FALSE，0，-(Q5 <> D5))

That formula also starts by checking the CheckID cell. If it's FALSE, the record is not in the database, so the result will be zero.

该公式还通过检查CheckID单元格开始。 如果为FALSE，则记录不在数据库中，因此结果将为零。

=IF(CheckID=FALSE,0,

= IF(CheckID = FALSE，0，

Otherwise, it compares the values in columns D and Q, to see if they are NOT equal "<>".

否则，它将比较D和Q列中的值，以查看它们是否不等于“ <>”。

--(Q5<>D5)

-(Q5 <> D5)

The result is TRUE or FALSE, and the two minus signs (double unary) change that result to a number (1 or 0):

结果为TRUE或FALSE，并且两个减号(双一进制)将结果更改为数字(1或0)：

• TRUE = 1
  TRUE = 1
• FALSE = 0
  假= 0

为标签上色 (Colour the Labels)

The labels have conditional formatting to change the fill colour:

标签具有条件格式以更改填充颜色：

• Green for new records
  绿色为新记录
• Orange if the value in column D is different from the stored value
  如果D列中的值与存储的值不同，则为橙色

The conditional formatting rule for Green (new record) looks at the OrderID and the CheckID cells:

Green(新记录)的条件格式设置规则查看OrderID和CheckID单元格：

=AND(OrderID<>"",CheckID=FALSE)

= AND(OrderID <>“”，CheckID = FALSE)

And there's a simple rule for Orange (updates) – the Difference cell for that row is not Zero:

橙色有一个简单的规则(更新)–该行的“差异”单元不为零：

=R5<>0

= R5 <> 0

提醒讯息 (Reminder Message)

Finally, a message appears below the buttons, with a bright yellow background, if there's a new or changed record.

最后，如果有新记录或更改过的记录，则按钮下方将显示一条消息，带有明亮的黄色背景。

That's the result of a formula in cell B15:

这是单元格B15中一个公式的结果：

=IF(OrderID="","", IF(CheckID=FALSE, MsgAdd, IF(AND(CheckID=TRUE, TotalDiff=0),"", MsgUp)))

= IF(OrderID =“”，“”，IF(CheckID = FALSE，MsgAdd，IF(AND(CheckID = TRUE，TotalDiff = 0)，“”，MsgUp)))

• No Order ID – empty string
  没有订单ID –空字符串
• Not in database – MsgAdd
  不在数据库中-MsgAdd
• In database, no differences – empty string
  在数据库中，没有区别–空字符串
• Anything else – MsgUp
  其他– MsgUp

The MsgAdd and MsgUp named ranges contain the messages, and you can change those, to whatever text you'd like. Maybe your co-workers need a little more encouragement to save or update!

MsgAdd和MsgUp命名范围包含消息，您可以将其更改为所需的任何文本。 也许您的同事需要更多的鼓励才能保存或更新！

获取数据输入表单警报文件 (Get the Data Entry Form Alerts File)

To get a copy of the workbook, go to the Data Entry and Update Form page on my Contextures site, and click on Download #6 (With Alerts). There are notes in the file, to explain how the alerts work.

要获取工作簿的副本，请转到我的Contextures网站上的“ 数据输入和更新表单”页面 ，然后单击“ 下载＃6(带有警报)”。 文件中有注释，用于解释警报的工作方式。

翻译自: https://contexturesblog.com/archives/2018/12/06/worksheet-data-entry-form-alerts/

phpcms 表单数据表