FIRST EXCEL FILE as reference data & ANOTHER EXCEL FILE as collected data. Is this possible?

Rachel_Anne
Participant III

I have an excel data of barcodes with corresponding names. I want to scan those barcodes that are available at the moment. Not all of the barcodes in excel are available at a certain time. Now, I want to collect the barcodes available at the moment in ANOTHER EXCEL. I donโ€™t want to sync it with my FIRST EXCEL because it is my reference. How can I do that? Iโ€™m still a newbie in this platform. I need help.

0 5 541
5 REPLIES 5

If you are thinking of do this in an automated fashion then unfortunately no. But you should handle that with scripting in Excel anyway.

If you are wanting app users to choose your collected data items based on the reference data, then absolutely you can do what you are suggesting.

When you add tables, you have the ability to specify which workbooks and which sheets each table references. So setup the workbooks and sheets as you normally would and add them into the app.

If you want to transfer data from the reference data items to the collected data, you can do that by using expressions in the โ€œInitial Valueโ€ or โ€œApp Formulaโ€ properties in the columns where you want the referenced data to migrate into.

Rachel_Anne
Participant III

Thank you so much for responding. Can you tell me what exactly the expression I need to use to transfer and collect data? I would really appreciate from you.

Sure! But the answer depends on how you are approaching the the problem. What do you have created so far? And how do you currently plan to identify the rows that need to be collected?

Rachel_Anne
Participant III

I have already created the excel file for item list which includes the weight, height, size and the barcode of each. Now, I want to scan the barcode of the items available in the bodega and list those items on another excel file with the barcode, weight, height and size attached to it in a certain time. Thank you so much.

Ok, I think I have fairly good idea of what you are trying to accomplish.

This is what I would do. Iโ€™m going to some level of detail. If anything doesnโ€™t make sense, let me know.

  1. Create 2 extra sheets - one is for the Collected rows, the second is simply used by AppSheet to perform your scans and lookup from the Reference data, maybe call it Scan. Make sure it includes all of the columns you wish to copy to the Collected sheet.
    This second sheet will only ever have ONE row that you use over and over again to scan. Why? Because once you scan, you need to decide what to do with the scanned info - write to Collected sheet or something else.

  2. Create a Form using the Scan sheet and save 1 row. Get the ID rom that row and place it into the Form definition under โ€œView Optionsโ€ - โ€œRow keyโ€ field. This sets the form to always open with this same row each time.

After creating Form against Scan sheet - Save a row
2X_b_b862db95493d30f5c9c07d56c1bd5b59b50071c3.png

Take Key value from saved Row and update the Form definition

  1. Create an Action (Data: add a new row o another table using values from this row) that determines if the scanned item should be saved to the Collected sheet. Attach the Action to the Form Saved behavior. Donโ€™t forget to prevent saving of an already saved item. See expression below.

Create Action to copy retrieved Reference data to new row in Collected sheet

Attach Action to Form Saved behavior

Example expression I used in my tester app

AND(OR(ISNOTBLANK([Image]), ISNOTBLANK([Name])),
         COUNT(SELECT(Collected[Barcode], [Barcode] = [_THISROW].[Barcode])) = 0
)
  1. Test that the records you expect are saving and others are not.

  2. Once tested, then you can setup the form to auto-re-open, auto-advance to the scanner and auto-save. This will allow to scan, scan, scan without the need of tapping other buttons or take other actions. Refer the sample app below for more details on how to setup the app in this manner. Note: I found that auto-re-open didnโ€™t work consistently.

https://www.appsheet.com/samples/An-app-that-repeatedly-scans-barcodes?appGuidString=42fd12f1-16db-4...

I hope this is what you are looking for and helps!!

Top Labels in this Space