Virtual Column to hold data

[anonymous]

Guys.
I have a dilemma with a csv table which changes every 20 minutes.
( Table A csv ) is uploaded to dropbox every 20 minutes and the data within it is read only. If any data is changed from the App it gets wiped 20 minutes later so within the App it is pretty darn useless if data needs to be changed on the App side, obviously great to read data from. I need to write data in one column within that table and for the data to stay put. Can I do this with a virtual column, ie, enter data in the virtual column and 20 minutes later when the main csv is overwritten the data from the virtual column still be present??

Cheers

[Steve]

Nope.

[anonymous]

I guessed that. I have a great app going and I’m bogged down by the read only table. So frustrating…

[Steve]

More details on what you’re trying to accomplish might produce better advice.

[anonymous]

Table A is read only, but one column which is not used and blank is called ID, it is never populated from the external application which uploads the csv. Can that field have a formula to read from another table (Table B) , so when Table A is uploaded and the App refreshed it will read a value? I am more than happy to add-edit data from table B, Table A [ID] only needs to read the value and show it.

(just to add. Table B has duplicate values in row JobID)

Cheers

[anonymous]

A VC with a formula should be able continuously pull in data from table B each time table A is updated externally. As long as the structure isn’t changing, it should work no problem. Just know that there is potentially a huge performance hit though depending on how you end up needing to craft a formula to pull said data. To me, it sounds like you’ll probably be using LOOKUP, but that depends on structure and what your pulling.

[anonymous]

Hi Dave, such a dilemma my app is so close but the read table has really held me back.
So, a LOOKUP.

In my pidgeon english.

Lookup the row in table B where JobID matches this row.
I don’t think the ‘Key’ values match so that could be an issue where JobID is many in the Table B

[anonymous]

Ok, so JobID already exists in both tables, Table B may have multiple of the same JobID, and I’m assuming you want the VC in Table A to contain a list of refs to the all the matching JobIDs from Table B?

If I’m right, and if Table A’s JobID is a key, then in Table B make JobID a Ref type. Point it to Table A. AppSheet should then automatically make a reverse reference VC for you. Doing exactly that. Let me know if your aiming to do something different.

[anonymous]

Hi Dave
Table A has a key (AutoID) which is generated from the external application which uploads the csv ( Microsoft Access ) and the value is unique and already populated. It is an increment numerical value. In this table JobID can also occur more than once hence I can’t use that as the key.

Table B has a key (ID) with a RAND between 1 and 100000, should I remove that Initial Value?
When I ref_row from Table A the two tables don’t align:

So with JobID holding duplicates in both tables the App has become difficult to control with both key values. If they natched I could use an inline view to show the field I need, however the inline view never shows any values because of the mismatch.

Its confusing …

[Steve]

Dave_Willett:

Table B has a key (ID) with a RAND between 1 and 100000, should I remove that Initial Value?

UNIQUEID() would be a much safer initial value expression than RANDBETWEEN(1, 100000). I recommend you change the initial value expression (but leave existing values as they are). Whatever the case, this expression and the values it generates is in no way contributing to your problem.

[Steve]

Dave_Willett:

So with JobID holding duplicates in both tables the App has become difficult to control with both key values.

You haven’t explained how JobID plays into all of this.

[anonymous]

Hi Steve
The imported table “qryTransport” or Table A just for this purpose holds details of collections and deliveries for our business. Our management system and all of the business revolve around JobID to identify repair cases, as unique as JobID is ( there is only one JobID for each case ) this particular table may have multiple entries of JobID, a collection or a delivery of a customers car will naturally invoke two instances, this is where a diferent key is vital. This table is uploaded every 20 minutes from Microsoft Access to dropbox so it is available to read the latest information.

Table “AppQuotes” or table B for this purpose is a quotes table and fully editable on the App side. This will hold many rows of items which will all have a JobID.

So the key to link both tables must have its own unique value such as a row number or a Now() expression etc. Its the link between the two which isn’t happy. If I refrows on both of the keys the inline view doesn’t show any data. Thats been a difficulty.

[Steve]

Is the ID column of the qryTransport table shown in screenshot the same as “JobID”?

[anonymous]

No Steve, its further down the page not shown.
Cheers

[Steve]

So what is the ID column of the qryTransport table for?

[anonymous]

It was meant to be used elsewhere in the Microsoft Access application, I never needed it and it comes across empty, I hoped to use it on the App side which I can, but when a new version is uploaded it is wiped of any temporary data.

[Steve]

Okay, so it sounds like you want a virtual column in qryTransport that contains a list of rows in AppQuotes where the JobID column value of each AppQuotes row matches the JobID column value of the qryTransport row? If so:

IFS(
  ISNOTBLANK([JobID]),
    FILTER(
      "AppQuotes",
      ([_THISROW].[JobID] = [JobID])
    )
)

[anonymous]

Steve:

IFS( ISNOTBLANK([JobID]), FILTER( “AppQuotes”, ([_THISROW].[JobID] = [JobID]) ) )

That kind of sums it up Steve, I’ll give that a test and let you know.

Thank you for the extensive assistance here, much appreciated.

[anonymous]

Works for me Steve