Virtual Column Expression

MauriceWhelan · 07-29-2019 09:29 AM

I need to create a virtual column to insert an expression to pull in some values from a previous record when entering a new record. I have used MAXROW() before and it works well but I have a slightly different requirement. The expression needs to contain a condition that pulls in the data from the previous record based on previously scanned Product Code. It is something like this I need

MAXROW(“Operations_Master_Check”,"_RowNumber", (Previously Scanned Product Code)

I need this condition in because the products may not necessarily be scanned in the order that they are presented in the google sheet.

Any advice would be great.

Steve

What do you mean by “previous record” and “previously scanned Product Code”? Previous to when/what? How would you identify “previous”?

MauriceWhelan

For example if I scan product code 0010 I want to populate some of the fields in the form from product code 0006 which was the previous record I scanned and updated.

Does that make sense?

LeventK

@MauriceWhelan
Have are you differentiating your records then if the order is not same than the gSheet?

MauriceWhelan

@LeventK The records are differentiated by the unique product code. There is only one entry in the table for any product code.

So I was hoping somehow when I was updating a record I could identify the previous record I saved and pull in some field values from there.

I don’t think this is achievable is it?

LeventK

@MauriceWhelan can you elaborate this a bit more please? Are you expecting to pull values from the table recorded previously for the same product code?

MauriceWhelan

@LeventK Hopefully this example might help.

I am recording data on a product into table Operations_Master_Check. The data capture is broken up into multiple slices to reflect the different stages of the processing.

The user scans a QR code (value e.g. 0250 - this is a unique code). Column [Measured By] is an Enum list of people and the user selects the user(s) who measured the product.
The user scans another QR Code (value e.g. 0288 - this is a unique code). To help speed up the data capture process I was hoping to use an expression to populate the [Measured By] column from the value selected in 0250 above.

The problem I have though is there is no guaranteed that products will be scanned/processed in sequential order so the 2 products above could be 30 rows apart in the Google Sheet. I was hoping we could still pass through a condition that would allow for the above example.

I hope this helps. It may have confused the issue more

LeventK

@MauriceWhelan
Here is what I have understood from your explanation. Please correct me if I’m wrong or mis-understood (sometimes I can be a jerk unfortunately due to the work load, so do apologize 😞

Your Operations_Master_Check table have X number of columns i.e. 101 (100 data + 1 Key column).
You have created Y number of slices from this table, each containing a subset of columns representing a different stage i.e. 5 slices, each having 21 columns (20 data + Key column)
By these bulleted above, the table might have multiple records for any scanned product code i.e. 0250 as users would need to create separate records for each stage of the product.

If that’s the case; its relatively easy to locate a previous record for any scanned code with the MAXROW() expression. However, I see that as user can scan multiple QRs one at a time, rather than a product’s last existing record in the table, you expect to read 1 or 5 priorly scanned products data as the latest record.

What I’m not sure - request @Aleksi and @Steve’s intervention here - if it’s possible to call back any data into a form if that record is not saved to the gSheet yet. However, if this scanning process is not a child record for a ref table, any scanned and saved form will already be synced to the gSheet (assuming that you are using delayed sync) hence MAXROW() should be able to de-ref that data.

On the case of child records, I (hope) remember (correctly) that in one of our client’s case, Aleksi had proposed me using the [Related Records…] virtual column with an ANY(SELECT(…)) expression to call back data from a saved child record where the data is not synced to the gSheet yet as the parent form has not been saved already. Something like:

ANY(SELECT([Related Records....][QueryColumnName], [ProductCode]=[_THISROW].[ProductCode]))

MauriceWhelan

@LeventK Thank you for such a detailed response. Your descriptions in point 1 and 2 are correct, however, point 3 is not exactly. Stage 1 slice is the first scan of the qr code and the creation of the record. Slices 2 and beyond require scanning the same qr code but are just updates to the original record created in slice 1. So there is only ever 1 row of data per qr code.
Apologies if that is the way you interpreted it and I have misunderstood.

If not does that change things a little?

LeventK

@MauriceWhelan
Gotcha. So let’s sum up now:

Your Operations_Master_Check table have X number of columns i.e. 101 ( 100 data + 1 Key column ).
You have Y number of slices from this table, each containing a subset of columns representing a different stage i.e. 5 slices, each having 21 columns ( 20 data + Key column )
With each QR code scan, rather than creating a new row of data, you are updating that particular product’s record with each subsequent stage’s data where your product code shall be the KEY column I assume.

The point I’m lost here is:
1.) when a product QR is scanned, are you trying to populate that particular product’s last (or one previous) stage data to the form?

OR

2.) when a product QR is scanned, are you trying to populate one previously recorded product’s data to the form? If that’s the case, how are you ensuring that the product code 0250 and product code 0288 are in the same stages?

I couldn’t understand if all the products are in the same stage when a user commences a scanning sequence or could the products be in different stages? i.e. product 0250 can be in stage2 but product code 0288 can be in stage4

MauriceWhelan

@LeventK Happy with first 3 bullet points. All correct and yes Product QR Code is key field.

I am then trying to achieve your point 2 above where I want some of the form for 0280 populated with e.g. whatever the value in [Measured By] was for previously scanned qr 0250.

You are correct that different product qr codes can be at varying stages of the process, however, if the user is scanning a code and i have a condition in place (if possible) that picks up certain values directly from the previously scanned code won’t there always be a value pulled in assuming the corresponding field in the previously scanned record has been filled in (which it has to be because it is mandatory)

If the user does scan a code and there is no corresponding value for that field pulled in from the previous record then they just complete as normal and all subsequent scans should contain corresponding values.

Am I understanding this correctly?

I am sure this cannot be good for your exhaustion. Apologies.

Aleksi

@MauriceWhelan I believe the order is confusing us to understand your request. Is it like this… you just want to read last row’s data as an initial value? It doesn’t matter what the QR code value is. Correct?

MauriceWhelan

@Aleksi yes you are correct. It doesn’t matter what the QR Code is. Using the example above if I have updated 0250 and saved and the next record I update is 0280 I want some of the initial values to come from 0250. But as I said 0250 could be positioned anywhere in the table. There is no guarantee it is the previous row to 0280.

Aleksi

You are talking about the update with the initial value. Do you mean when adding new records?

MauriceWhelan

No I do mean the update. The only part of the process I need to implement this logic is in slice 2. Using the example above both records would have been created in slice 1 and are now being updated in slice 2.

MauriceWhelan

@Aleksi , @LeventK Attached is a screenshot of some completed data.
Pre-Fabrication is slice 1 and the creation of each record with unique MA number. The products then move on to Fabrication (Slice 2) and [Measured By] is added but for this slice the records could be updated in any order e.g. [Measured By] could be added for MA0061 first and next product scanned could be MA0039. So what I was hoping to do is when MA0039 is being updated in Fabrication (Slice 2) I could pull in the the value of [Measured By] from MA0061.

As you can see from the data the MA numbers could be scanned ina ny order.

I hope this helps explain a little more.

LeventK

@MauriceWhelan
So basically you are seeking for the latest saved/updated data in the gSheet to be fetched as an initial value to the app. Correct?

MauriceWhelan

@LeventK Exactly

LeventK

@MauriceWhelan
The only possible option would be setting a Change Timestamp column first, and then set a Virtual Column with MAXROW() expression associated with this Change Timestamp column, which will return the Key column value of the lately updated row. And then you can de-ref this Virtual Column in your initial value.

MauriceWhelan

Thank you @LeventK. Let me see if I can get the desired behaviour this way.

Aleksi

You can create the same behavior with a normal DateTime column as well. Every time when the record is modified, the appformula NOW() would be updated.