Lookup/Filter Then report

AppSheet AppSheet Q&A
,
1

Hi Guys,

Need advise -

I have two tables

Table 1 - ID, PRODUCT, ITEM, WEIGHT -ITEM Is a Ref_lookup with WEIGHT pulling trough from the Ref_Lookup

TABLE 2 - ID, ITEM, WEIGHT

I need to do a report (xls or PDF) that displays a list of ITEMS from Table 1 where the WEIGHTS from Table 1 and Table 2 no longer match. If a WEIGHT from Table 2 ITEM no longer corresponds with the Table 1 ITEM WEIGHT, it must appear on the report.

Any ideas or starting pointers on how to accomplish this? I did a few searches and plaid around with a few ideas but nothing is giving results.

2

To generate a report that lists items where the weights in Table 1 and Table 2 do not match, you can follow a series of steps in AppSheet. First, you need to create a slice that filters out the rows where the weights in the two tables are not matching. To do this, go to Data > Slices, create a new slice named MismatchedWeights, and select Table 1 as the source table. In the row filter condition, use the expression [WEIGHT] <> LOOKUP([ITEM], “Table 2”, “ITEM”, “WEIGHT”) to filter items where the weights differ.

Next, you need to create a view to display the mismatched items. Go to UX > Views and create a new view named Mismatched Weights Report. Set the view type to “Table” and select the MismatchedWeights slice as the data source. Configure the columns and appearance as needed to ensure the report is clear and informative.

Finally, set up a workflow or bot to generate and send the report in PDF or XLS format. Go to Automation > Bots and create a new bot, naming it appropriately. Configure the bot to trigger on a specific event, such as a time trigger to run at regular intervals or a data change event. Add a “Run a task” action to generate the report, setting the task type to “Create a file” and selecting the MismatchedWeights slice as the data source. Choose the desired file format and configure the template to format the report as needed. Set the output destination, such as emailing the report to specific users or saving it to a designated location.

By creating a slice to identify mismatched weights, a view to display the results, and a workflow or bot to automate report generation, you can efficiently manage and report discrepancies between the two tables in AppSheet. This approach ensures that you have an up-to-date report of items where the weights no longer match, helping to maintain data accuracy and integrity.

3

Needed a few tweaks, but other than that, this worked perfectly! You are a legend!

1 Like
4

This is more a community posting - for those struggling to understand how to add slice data to a PDF report you need to know that the slice data pulls through from the Main Table data and references the Slice Data in the actual template and not the Event or process. I was able to get the following from CHATGPT:

Template Setup

Since you can’t directly select the slice in the task configuration, you must reference the slice within your template using AppSheet expressions.

Example Template

Here’s an example of how to set up your template to reference the slice data:

<<Start: SELECT(Sliced Data Table[KeyColumn], TRUE)>>

Column1 Column2 Column3
<<[Column1]>> <<[Column2]>> <<[Column3]>>

Explanation:

  • <<Start: SELECT(Sliced Data Table[KeyColumn], TRUE)>>: This expression selects all rows from the Sliced Data Table slice.
    • Replace KeyColumn with the primary key of your slice.
    • The condition TRUE ensures all rows are selected from the slice.
  • Inside the <> and <> tags, reference the columns as usual.

Hope this helps!

2 Likes