Get an automated fill with multiple conditions

Hi…
I’m trying to get an automated fill based on my table called database that has collumns called [class][type][classification][Height][wide][length]. The thing is, I try to autofill the [class] collumn based on [type],[height] and [wide]. for example if the [type] value is square than it will look into the [height] dan [wide] collumn, if the value of those collumn is 4 it will autofill the [class] collumn with A1 etc.

I have use this expression ANY( SELECT(Database[class], AND( ([Height] = Database[Height]), ([Wide] = database[wide]), ([type] = [_ThisRow].database[type])), FALSE ) ). But it returns with error message "Cannot compare Number with List in ([height] = DATABASE[height]).

hope you understand my question
regards

0 16 807
16 REPLIES 16

Lynn
Participant V

Hi @mijwadul_ihsas
Maybe try something like
ANY( SELECT(Database[class], AND([Height] = [_THISROW].[Height], [Wide] = [_THISROW].[wide], [type] = [_ThisRow].[type]), FALSE )
I havent tested it.

Thanks for the quick reply…but It return with the error message “Unable to find column '_thisrow].Database[Height”, though the collumn does exist.

Did you try removing Database?

The DATABASE table is exist in the same app as the form table. I don’t try to remove or delete it.
Or maybe I should change the logic, how about if the type is square and the (height+wide)/2= between 1-10 it Will auto fill with A1 value?. So I Will no longer need the database table. The samething goes if the (height+wide)/2= between 11-20 it Will auto fill with A2 value. And so on… Maybe it Will need a longer expression but I think it Will solve the problem. Do you have any idea on how the expression should look like?

I have type the following expression and it is work.
Ifs (and ([type] = “square beam”, ([Height] + [width]) / 2 = 14-19), A1, and ([type] = “square beam”, ([Height] + [width ]) / 2 = 20-24), A2 Small)

but the class collumn hasn’t autofill yet…I meant it just still blank however I make changes into height and width collumn.
any idea?

Steve
Participant V

This is your original expression, reformatted for my clarity:

ANY(
  SELECT(
    Database[class],
    AND(
      ([Height] = Database[Height]),
      ([Wide] = database[wide]),
      ([type] = [_ThisRow].database[type])
    ),
    FALSE
  )
)

What you probably want is:

ANY(
  SELECT(
    Database[class],
    AND(
      ([Height] = [_ThisRow].[Height]),
      ([Wide] = [_ThisRow].[wide]),
      ([type] = [_ThisRow].[type])
    ),
    FALSE
  )
)

See also:


here is my database spreadsheet

I want to fill the [kelas] collumn based on the database[jenis] and database[diameter], but the diameter is actually a range of value from the calculation of ([tinggi]+[lebar)/2 (those are collumn in my spreadsheet). I have try all of those available options mentioned above, but none of the works. maybe you could give me something for my problem.
thanks

Looks like you want an app formula:

Yes… I type my expression in app formula tabs but it still doesn’t works

From the error message, it appears your Database table does not have a Diameter column.

It has the diameter collumn as the picture I sent Above…

What is the KEY for the table in the picture below?

I didn’t see the [tinggi] and [lebar] column in the above table, maybe should be switch somehow to check the diameter range on the database table based on calculation of [tinggi] and [lebar] which are two of the inputs in the form? (I assume that the name is form table.

It is easier for me to imagine if you are able to do this:

  1. Create a virtual column in the form table named [Diameter], with app formula calculation of ([Tinggi]+[Lebar])/2

  2. Update the formula of the [Kelas] in the form table with:

ANY(
	SELECT(Database[Kelas],
		AND(
			[jenis]= [_THISROW].[jenis],
			[Diameter]<= [_THISROW].[Diameter]
		)
	)
)

The key for the table Above is the row number

I have Did the step you told me to…but it Sri return the same error as before

You might want to change the KEY of the database table to a unique value and NOT using row number as the key. Please tried that.

I have found the culprit…turns out my virtual collumn named as Diameteer, it has double E. And its now work perfectly as I want. Thanks for the help…Appreciate.

Ah, glad to see it working now. However, I recommend not to have _RowNumber as the key.

In addition, I saw your other post, it seems similar, you might want to just close that post. And just post another questions you might have on a new post later.

Top Labels in this Space