Hey folks, I need help with an expression. ...

Miranda_Lubarsk · 03-05-2018 04:00 PM

Hey folks,

I need help with an expression.

I have a timesheet, and I would like to figure out the total cost of the plants.

I have columns where the plant size is selected by the crew in a form, and then a separate column with that item’s quantity.

I would like appsheet to use the value of that column [Plant Size (1)] to look up what the equivalent price is, located in another spreadsheet.

The Column [Plant Size (1)] is an Enum list right now.

But equivalent place sizes and their prices are listed in this other spreadsheet.

Nothing is a key in either spreadsheet.

My problem with using the LOOKUP expression is that it requires me to lookup a specific text value, but I want to refer it to the value of that column in the same row.

How do I do this? Thanks! Miranda

David_Hopkins

@Miranda_Lubarsky 4355398@gmail.com

promise

Miranda_Lubarsk

@David_Hopkins Ok, adding you now, thanks!!

Fyi, I just tried something new in the Plant Size (2) column of Timesheet table, but all the other ones are what I was using before.

What I tried did not work…again.

Looking forward to your thoughts!

David_Hopkins

@Miranda_Lubarsky Also… if you have hangouts, ping me there… using that email… we can screen share if needed there to walk through the app.

David_Hopkins

@Miranda_Lubarsky while reading back through the comment stream and looking at your data. i have a few observations.

First off…

um … wow… thats some application with a crazy spiderweb of data. I hope you have a db schema or chart for it all. All in all, it’s pretty awesome!

Secondly, I would consider breaking up some of that data and using reference tables to inter-link much of this information. Reference tables will reduce the number of REF links (but increase the number of tables). However, it will make everything easier to read, not to mention to troubleshoot.

Happy to give you a lesson on DB design and reference tables if you’d like.

Finally, here is what I found in my looking… (and changing nothing)

I don’t see a field in Master Garden List called Tray Size or Plant Size … so the reference will fail.
references work based on keys, so not having one in Design Number for 2018 could be costing you. Can you create it as a UniqueID(). Worse case would be to use the RowNumber as the key. You do have updates & adds enabled for that table which could cause troubles as well …

I hope this helps…

Miranda_Lubarsk

@David_Hopkins hey, and thanks!

I have been working on this baby for a long time, but it’s almost planting season so looking to get it wrapped up.

A few questions, what expressions were you talking about failing in the master garden list? I think i mispoke in my note before, I meant to say Design Numbers for 2018[Tray size].

Also, i do have a key in Design Numbers for 2018, it is called “Plant ID” and is a concat of plant & variety.

Still looking for help in figuring out how to disconnect the de-ref of Plant Size in timesheet table…any thoughts on that?

Thanks so mch for your help!

David_Hopkins

@Miranda_Lubarsky

First disconnecting the De-ref should just be unchecking the box on that column in both that table and the referenced table.

As for the key in Design Numbers…

Using a _computedKey can work fine as a key, however i have found (and im dealing an issue right now) where computed keys don’t offer the same reference abilities that standard keys do. (i.e. https://plus.google.com/+DavidHopkins4355398/posts/VGc5konY838)