Help with an Expression required

[DPM_reports]

Hi, i have a sheet with two tables

Table 1

Here i have columns like Lot No., Storage Bin(A Section), Location Availability ?

Table 2

Here columns are Lot No., Status,

What i want help with is in Table 1 Location Availability ? i want top put in an expression where if the same lot no. as in Table 1 in Table 2, where Status is completed, i want the expression return data as available, or Unavailable.

I dont mind if i get a Google Sheet Expression also

Can anyone help me in this regard.

[LeventK]

@DPM_reports
Try with this:

IF(
	LOOKUP(
		[_THISROW].[Lot No],
		"Table 2",
		"Lot No",
		"Status"
	) = "Completed",
	"Available",
	"Unavailable"
)

[DPM_reports]

can u also tell me the Google sheet expression for the same…

As i tried your expression which in this case works as required…

But my solution requires the expression to be in the sheet rather than appsheet for better visibility of results

LeventK:

@DPM_reports
Try with this:

IF(
	LOOKUP(
		[_THISROW].[Lot No],
		"Table 2",
		"Lot No",
		"Status"
	) = "Completed",
	"Available",
	"Unavailable"
)

[LeventK]

It’s hard to construct that expression without knowing your gSheet and Table structures actually. On the other hand, I don’t advise use of Spreadsheet Formulas, where it will need the app to be synced first to fetch the calculated results from the spreadsheet. Using AppFormula instead will be more direct and dynamic. Besides use of spreadsheet formulas will increase your sync time as well. Worth reading below pages:

help.appsheet.com

Spreadsheet Formulas

How formulas you write in your spreadsheet impact your app creation process

help.appsheet.com

Mismatched Spreadsheet Formulas

Diagnosing Mismatched Formulas in a Column

[DPM_reports]

My problem in this case will be that once data is entered in table 1 …user will have not edit access again…and this column value to change will require the user to go into it and edit…if it’s in the gsheet…the data of this column will not require user access to edit and hence will serve my access

[LeventK]

@DPM_reports
I don’t think I can follow you correctly here. Can you please briefly describe how data is entered into your app? What’s the sequence? How you have structured your tables and your gSheet back-end? And I haven’t understood this part as well:

DPM_reports:

My problem in this case will be that once data is entered in table 1 …user will have not edit access again…and this column value to change will require the user to go into it and edit…

Appreciate your explanation.

[DPM_reports]

This column where i want this formula to entered will have its value altered after the next process data is entered by another department…and when a cloumn in that sheet changes its value, the value in the 1st sheet changes…

hence i said if the formula is appsheet based, it will be altered/changed only when the user goes to edit mode and saves that data…only then the value changes…which would be not feasible for me…

if the value is changed via a Google sheet formula, the user does not have to goto edit mode to see the changed value, it automatically does it…

Hope your getting my point…

[LeventK]

@DPM_reports
I got the point, but still you don’t need the formula in the gSheet. You can use the expression in the Initial Value, constraint the edit for the first time the record is created via Editable_if and you can use the Reset on Edit to automatically reset the initial value and the new value will be calculated automatically.

Besides, it’s doable with creating a DataChange workflow and assinging it to the FormSaved action as well.

There are many alternatives for manipulating gSheet data depending on what you are trying to establish.