Use OCR to populate multiple rows of reference list

[Tom_D_Auria]

I would like to use OCR to extract line items from an order that are currently entered manually. My app is set up so the order is created as a single row with information like Customer, Due Date, jpeg of order etc… but then a separate table that references the order number (key) includes the multiple items on the order. Is it only possible for OCR to populate the columns of one row, or could I have it reach into a referencing table and add rows as the model requires?

[tony1]

@Tom_D_Auria Right now OCR models can only extract a single row at a time. So for example:

• Supported: Extract the customer name and order date from an invoice
• Not supported: Extract a collection of line items from an invoice, with each line item as a related row

It’s a compelling use case though, and something that we’re considering.

[Ed_Cottrell]

Hi Tony,

Was there any further advancement on OCR in relation to extracting into multiple rows? We have a use-case in the telecommunications space that would be a huge help. We have drawings that have a number of codes on them, every single time in a red square box. (Very consistent optics).

It would be amazing to screen shot a drawing and have the OCR model pull all the codes off the drawing into a separate row each. I’ve attached a screen shot of a drawing and circled the red boxes in blue - as example. Each box has a code in it on the top line (like “SD-02-03-04”) and a quantity on the bottom line (like “1”).

Is it too complex?

I can see @FREDY_ORTEGON 's suggestion might possibly work, but we cannot use it as we don’t know the maximum number of codes we might get on a drawing. (So can’t fix the number of table columns, as per his single row approach).

Thanks!

[tony1]

Hi @Ed_Cottrell, no futher advancements to the OCR feature yet. Adding @Arthur_Rallu fyi

[Ed_Cottrell]

Hi Tony, thanks. @Arthur_Rallu Hi - what do you think of the telco use-case above? Possible? Too complex? Cheers!

[tony1]

For our current capabilities, it’s probably too complex. Right now our OCR functionality is limited to fixed-format documents (e.g. a form where all of the fields are at a fixed position).

Another workaround for you might be to use the OCRTEXT function, which extracts the raw text from the image. Then you could try to extract the IDs you’re interested in using a sheet formula REGEXEXTRACT - Docs Editors Help

Adding @Philip_Stephens fyi

[Philip_Stephens]

An alternate approach, but is also pretty complex, would be to combine ocr+automation. Depending on how your images are structured, you could pull the data into a separate flattened table and use automation to restructure that into the nested construct. Our current invoice and receipt solutions do support this type of behavior (parsing multiple line items from a single image) and we are looking into how we could follow a similar approach and allow users to develop their own custom models.

[Fabian_Weller]

tony:

Then you could try to extract the IDs you’re interested in using a sheet formula REGEXEXTRACT - Docs Editors Help

And upvote here

REGEX() expressions and an optional occurrence parameter for SUBSTITUTE() Feature Requests

@praveen @tony Is it possible to have powerful expressions like: REGEXMATCH() (returns a TRUE/FALSE) REGEXEXTRACT() (returns TEXT) REGEXREPLACE() (returns TEXT) I’m aware that the EXTRACT() expression can perform alike but at the end this function returns a list and it’s a bit limited in nature. Additionally we need an optional occurrence parameter for the SUBSTITUTE expression which can save us from creating nested SUBSTITUTE expressions where expressions can become cleaner. SUBSTITUTE( t…

[FREDY_ORTEGON]

how many maximum rows should you add in the peido detail table? …
I did something similar

[Tom_D_Auria]

Probably a max of 10 items.

[FREDY_ORTEGON]

You can prepare an OCR system that extracts data from your invoice items and saved them in a row as the example.

You must also create a table that contains the details of the invoice:

Next, you must create 10 actions. (where each action will copy the data of each item with the type of action - “Data: add a new row to another table using values ​​from this row”) for example:
“AtionItem1” this action will copy the item 1 data in the detail table. Then “AtionItem2” this action will copy the data into the new table of item 2 and so on.
Then, create an action that executes these 10 actions.

Try this to see if it works, the challenge is to be able to separate the items from your invoice with the OCR.

I hope I can help

@tony it is possible?

[Fabian_Weller]

@FREDY_ORTEGON Excelent! I’m pretty sure that this would work. This would be like digitize a document
But as you said: Every invoice must look the same so that OCR can recognize it.
Thank you very much for sharing this idea.

[Ed_Cottrell]

Thanks all. @tony the regexextract idea is logical - and I can see how it would apply in my use-case:
Extract all text on the drawing/image into a single cell using OCRTEXT, then pulling all the ‘schedule of rate codes’ out of that text. It would be a huge repeating manually crafted formula to check for all codes out of my list (which is about 200 codes long)… Doable, yes - but not practical I suppose.

One tripping block is, I also need the quantities alongside the codes (the number below the code inside each red square box).

Nonetheless, all good ideas here and I appreciate it.

@Fabian I voted for @LeventK’s regex() expressions feature request - I agree, many AppSheet users would find use in many of these powerful functions, and my preference is always to work ‘in the app’ rather than with sheets formulas, or external processors/apis etc. One place to control and update and debug the app!

[tony1]

@Ed_Cottrell If you know the list of possible codes, it might be simpler… You could just check to see if the code appears anywhere in the OCR’ed text. Create a virtual column called OCR Column with formula OCRTEXT([Image Column]). Then add another virtual column like SELECT(CodesTable[Code], CONTAINS([_THISROW].[OCR Column], [Code])).

Extracting quantities alongside the codes is tricky, though.

[Ed_Cottrell]

Ahh yes, thanks @tony - that would work.

The problem is getting the quantities alongside the codes…

If the OCR would detect multiple locations in an image, that would be cool, as every time the code/quantity is in the same geometric layout (the red boxes).

Another, application of the concept could be to take a photo of a group of people each with a name-tag (e.g. at a business conference or similar), and one could use OCR to check all of those guests into the event from one photo… a bit like face-detection on facebook or some phone cameras…

Anyway, just food for thought, would be cool if possible in future. (I realise the feature backlog will be long!)

Cheers.

[Fabian_Weller]

Hi @Ed_Cottrell I’m pretty sure you are able to do this with Integromat.

OCR detection with XY Coordinates Feature Requests

Using the Google Cloud Vision API and Integromat we are able to extract Text / Number from an image and add XY Pins automatically. You will get something like this without any manual work: [image] How it’s done: I would like to use this directly in AppSheet. Adding @tony @Grant_Stead @Bahbus @tsuji_koichi @Rich

[Ed_Cottrell]

Ohh, interesting! Thank you for that @Fabian I checked out your video - it looks really impressive.
I will take a look at this in some detail, and report back my findings!

I wondering if integromat / google API can detect the text that is always inside a red box (as in my case), or in your demo case - limit the detection to only numbers inside a circle… (some kind of repetitive geometric flag)