Hide rows Values IF they equal a value

BlakeHammond · 01-06-2020 08:16 AM

Hi

I am trying to hide a row value so it doesn’t appear in a table lookup. I will explain

I have A app called Live orders and a newly created app call job sheets.

In job sheets app i create a new job by selecting certain details from the live orders app. Like Job Number Customer Name ETC

When I mark that job as complete it still appears in the list of orders i can select. I would like the list only to be orders that are not complete.

I tried using filter or select but I could only get them to work if the column is the key. which it isn’t

Blake

Steve

A common way to achieve what you want is to create a slice that hides the desired rows and display the slice rather than the table itself.

BlakeHammond

Hi Steve

That works for a view but I am trying to do it when I am creating a new row. so I can only select items that are not marked as completed.

Steve

How are you generating the list of rows for the form currently?

BlakeHammond

live customer list[job number],but there brings in all data and I just want to see ones that arent marked as complete

Bahbus

SELECT(Live Customer List[Job Number], NOT("Complete"=[Status]), TRUE)

Substitute “Complete” and [Status] for whatever it is you are using.

BlakeHammond

Wondered when I would see you pop UP . I did try this expression but because there is over a thousand rows, when trying to sync the app on mobile it takes awhile … is this the most efficient?

Bahbus

I think FILTER would also work just fine, but I’m not sure if it’s any faster/more efficient. Also it depends on how you using it. If it’s just a Valid If in the form to select from, it should be pretty fast. Now, if you put this formula in a VC for a table with over 1000+ rows, then it might take a performance hit.

If you want to try it would be FILTER("Order List", NOT("Complete"=[Work Complete]))

Alternative, and maybe more efficient for expressions, you could change [Work Complete] to type Yes/No (aka True/False). Then change the data in that column to either True for complete, or False for incomplete. That way, your formula can just be:
SELECT(Order List[Quote Number], NOT([Work Complete]), TRUE)
or
FILTER("Order List", NOT([Work Complete]))

(also you can always expect me to show up during weekdays eventually)

Steve

FILTER(table-name, ...) is equivalent to SELECT(table-name[key-column], ...). The advantage with FILTER() when offering advice is you don’t have to know the key column name or remind them to substitute appropriately. Performance-wise, there’s no difference.

Bahbus

Fair enough. I figured they would be equivalent performance.

BlakeHammond

the actual expression i was using is Select(Order list[Quote Number], ( isblank([Work Complete])))

BlakeHammond

OK so run into a little snag… if i use the select statement to select customer names where they haven’t been marked complete.

I then can’t use “order list[Site Name]” in valid-if in [site name] because it lists all the sites not the one that corresponds with [Customer Name].

but if i put “order list[Site Address]” into Valid-if in [ Site Address] that displays only the address that corresponds to [Site Name] so the select statement stops it working.

Any suggestions

Blake

BlakeHammond

^^^^ Can Anybody Help ??

LeventK

@BlakeHammond
Try with:

SELECT(
	Order List[Site Name],
	[Customer Name]=[_THISROW].[Customer Name]
)

BlakeHammond

Hi @LeventK

I used that expression above and it gives me the same issue i am struggling with.

Thanks

Blake

Bahbus

What is the goal with that Valid If, and what column is it going on?

BlakeHammond

let me see if I can break it down.

I am making a job sheet app so :

The column [Customer Name] needs to equal customers that have live orders but have not been completed, hence [Work Complete]<> “True”.

I also that a column [Site Name] which is the site for that customer we have an order for i.e London Office, Europe Office. This column should only list the site tied to the customer that we have an order for. I was using( Order List[Site Name] ) to achieve this.

The issue :

When i use the select funtion inside [Customer name ] to display only customers that haven’t been complete. which is correct.

The [Site Name] column then lists all sites not just the ones for that customer.

If i remove the select function from [customer name] and use (order List[Customer Name]) instead. Column [Site Name] then only displays sites for that customer.

I hope this helps

Bahbus

And are you saying that using this has produced the same results as just Order List[Site Name]?

BlakeHammond

Gave me the desired answer only shows customers that are not marked as complete.

but that means that column [Site name] shows all sites not the ones just for that customer

if i put ( order list[Customer name] ) into the [Customer name] Column instead of the select expression column [Site Name] then only show the sites that correspond to that customer.

BlakeHammond

so what should happen is

Customer 1 is in progress

Site 1

Site 2

customer 2 is complete

Site 3

Site 4

When i click on the [customer name] column i should only see customer 1 because it is in progress

then when i click on the [Site Name] Column i should only Site 1 & Site 2 because they belong to Customer 1

what is actually happening with the select expression is

When i click on the [customer name] column i only see customer 1 because it is in progress

but when i click on the [Site Name] Columns I can see Site 1,2,3 & 4

LeventK

Change the expression like this:

SELECT(
	Order List[Site Name],
	AND(
		[Work Complete]=true,
		[Customer Name]=[_THISROW].[Customer Name]
	)
)

BlakeHammond

Ahhh good shout so put a select statement in each column that links to the customer instead of using just order list[Site name].