Re: How to filter selection according to criteria

ADAJALAN · 10-09-2021 07:51 PM

"I have a transaction form like the following,
The problem is I only want to display the months that have not been paid, currently it still displays the entire month selection

And in the form that currently exists, I only managed to give a valid error if the month period has been paid”

WillowMobileSys

In the Valid_If expression of the [Pembayaran Bulan] column, you will want to SUBTRACT the list of months paid from the list of all months. Since you already have a column reflecting the months paid, this is fairly straight forward using the “list subtraction” operator like so:

LIST("Jan", "Feb", "Mar", "Apr", "May", "Jun", 
         "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") 
- 
SELECT(TABLE[Pembayaran Bulan], 
               AND([Kode Langganan] = [_THISROW].[Kode Langganan], 
               [Jular] = [_THISROW].[Jular]))
+ [_THISROW].[Pembayaran Bulan]

Note: I guess at the table selection criteria. You may need to adjust it to select the desired rows.
The “+” adds back the month for the current row as a valid value. May not be necessary but it doesn’t hurt.

ADAJALAN

thanks for the feedback, I’m not sure where the error is, but I’m really glad you can help again

WillowMobileSys

What is the defined type for column [Pembayaran Bulan]?

ADAJALAN

Yes, right,

WillowMobileSys

??? I was asking for the definition type of the column.

ADAJALAN

for the problem above has been resolved properly, with the formula given without the +. thank you I say

but now there is 1 new problem, where i want to do validation, showing only [Kode Pelanggan Full] and [Pembayaran Bulan]

with criteria, column [Jan] to [Dec] is empty

WillowMobileSys

To select rows where ALL months are empty, you will need to check each of the month columns like so:

SELECT(TABLE[Kode Pelanggan Full], AND(ISBLANK([Jan]), ISBLANK([Feb]),...,ISBLANK([Dec]))

NOTE: Fill in the rest of the months where the “…” is!

ADAJALAN

oke thanks

ADAJALAN

Hi, I want to continue the discussion again.
can list subtraction work with subtracting data with enumlist option,

like this for example:

ADAJALAN

WillowMobileSys

Yes, it should work just fine. Give it a try!

ADAJALAN

but data with enumlist results like this: Oct,Nov,Dec

not to be deducted from list data : LIST(“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”,
“Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”)

any suggestions?

WillowMobileSys

I don’t understand. What is the end result you are hoping to get back?

ADAJALAN

i want data " Oct,Nov,Dec ",
can already be a deduction from the data list,

but this only works when the data contains only examples of "Oct " or “Nov” only, can’t combine data like "Oct,Nov,Dec "

WillowMobileSys

If your column is an EnumList and it has the value list of “Oct, Nov, and Dec”

You should be able to to perform a LIST subtraction like so:

LIST(“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”,
“Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”) - [EnumList Column Name]

And end up with the result of:

“Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep”

Is that not what you see happening? If not, what IS happening?

ADAJALAN

I use valid data, valid if =

or(
IN([Path],Select(user[path],usersettings(level)=“Admin”));IN([Path],Select(user[path],[Name]=[_THISROW].[Sales Name], usersettings(level)=“User”))
)

but the column shows only 1 item, and it doesn’t match.

Whereas if I use valid if =
Select(user[path],[Name]=[_THISROW].[Sales Name],usersettings(level)=“User”)

Columns can display appropriate options.

in this condition I want to do a multiplication , if Ex. A then must use the formula A, and the result belongs to A while if Ex. B then you have to use formula B… how is the valid if formula that can work, hopefully someone can help, I’m very grateful

WillowMobileSys

Your expression has a semi-colon in it instead of a comma.

Also for User/Admin, you probably want to use the USERROLE() function but this only works if you are white-listing the users in the app.

ADAJALAN

Yes that’s right,
that’s the result I want.

and I combine this formula like this, the result is not as above.

LIST(“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”,
“Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”) -

SELECT(Transaction[Payment Month],and(YEAR([Date])=YEAR(TODAY()),[Subscription Code]=[_THISROW].[Subscription Code],or(isnotblank([Subscription Status]),isnotblank( [Amount])))
)

Maybe there are inputs and suggestions from my formula above?

ADAJALAN

in the [month payment] column, the “Jan” value should not be displayed again

WillowMobileSys

This expression looks ok. Have you tested the SELECT() to make sure it is returning the list of values you expect?

ADAJALAN

yes, this expression works, but only for inputs like “Jan”, not for “Jan,Feb”

ADAJALAN

Looks like the attached photo,
results are not as expected.
no list is deducted from the input process