Picking up Column Header from Row Data

[Robert_wilde]

good evening all! thought i’d share a problem i am totally failing to solve at the moment, - one with a number of potential solutions but i’m just unable to make(bodge) any to fit - i suspect the greater wisdom on here will make light work of it… ;-D

i have made a fishing match/competition calculator… something i’ve had in XLS for years… and really enjoyed making an appsheet front/UI to it. lovely stuff… and now am fathoming up new ways to use the data collected.

data collective has to be simple for obvious reasons (Bankside/cold/wet etc) and have a simple form to register what fish and weight can be input with it

gives me a new row in the table on here…
[1] - image below

this works a treat! and can do lots with this back in Sheets…
but looking at column J - in different areas i need to pick up what ‘type’ was recorded at that point.
eg.
[2] - image below

have managed it with a bizarre expression and yet another lookup table in an unused sheet.
don’t ask how long it took to work out how to display Lb/Ozs

anyway in sheets/xls - its easy… and assumed i could add that as an Initial View to a virtual column… or even as straight_txt in the form // no can do… the expressions dont accept MATCH among a few others.
just look up for the 1, give me the column header… run out of methods i know about.

another data collecting front i made for mileage/mpg - has a Sheet that automatically adds the previous rows formulas… the orange columns collected in appsheet and sent over, the pink rows are empty but on form send they magically appear and maybe this is the answer but for the life of me don’t know how that works either! but this works a treat too!
[3] - image below

anyway - thought i’d chuck this out there… as i feel my very soul ebb away. cheers.

[WillowMobileSys]

Hi @Robert_wilde! Welcome to the AppSheet Community!

It’s not clear what you might enter on a single row in your sheet. Would you register multiple species of fish on the same row? If so, what would the “fishname” column contain then?

If you only record a single fish species per row, then why not just have the “Fishname” column instead of a separate columns by species?

Also, are you trying to “REPLACE” the spreadsheet? If so, I would highly recommend the change above in that case.

Robert_wilde:

the orange columns collected in appsheet and sent over, the pink rows are empty but on form send they magically appear and maybe this is the answer but for the life of me don’t know how that works either!

If the orange columns only are inserted by AppSheet and you have columns in the app for the pink columns, then likely what has happened is that you had formulas in the sheet for those columns and AppSheet sucked in those formulas into the app. They would be placed automatically into a field named “Spreadsheet Formula” within each column description that had the formula in the sheet. See the image below. These will cause automatic calculations for the columns.

[Robert_wilde]

good morning… oh my… have to say - both amazed and annoyed equally at the same time… lol. have checked my other app for the AUTO-COMPUTE box and indeed it is there, amazed it was pre-populated - good work! and frustrated annoyed (at self mainly) for not seeing this come through and isnt just ‘magic’! as really couldnt understand how it was doing it…

so with that - massive thanks for this…

re: the other stuff. slightly moot now! i had ‘solved’ it with a silly amount of cascading sheets - bit concerned the lights dim in the server farm when i add an entry, i can now however revisit this with my new knowledge tidy those up considerably

you wouldn’t (but can) add more than one fish in a row. ideally - it 1 in one box and 0 is all the others - this way the Weight is associated to the Fishname.

the form is setup as initial views in all 0 and a button for each fish ‘marked’ as a 1

thanking you so much…

[Steve]

Robert_wilde:

re: the other stuff. slightly moot now! i had ‘solved’ it with a silly amount of cascading sheets

I’ve been working with AppSheet for four years an I STILL find myself doing things the hard way only to realize after the fact that there’s an easier way to do it. You are NOT alone!

[WillowMobileSys]

Robert_wilde:

for not seeing this come through and isnt just ‘magic’!

No “magic” here. It’s all “muggle-based”!

There is always a rational explanation. If you encounter something and want to know why it behaves the way it does, just reach out to the Community BEFORE you spend lots of time on it. It may be that some things are “black box” and happen by AppSheet automatically. At least you’ll know.