Filter multiple condions where one condition is in another SQL table

[MarcoSchade]

Why does this not work?

Filter(INVENTTABLE;Len(ITEMID)=7 And StartsWith(ITEMGROUPID;"1000") And StartsWith(ITEMID;Text(Dropdown1.SelectedText.FabrikNummer)) And INVENTTABLEMODULE.BLOCKED.Value=0)

Im am filtering my INVENTABLE and the first three filters work perfectly. However, when I try to filter using another table whether its value in column blocked is equal to 0 I get an error and no data is showing. 

The ID column in these to table is what makes the relation. 

How do i do this?

[Amik]

Hi @MarcoSchade,

What you're trying to do will not work unless you're using are Dataverse tables with a relationship (one-to-many, many-to-one) etc.

Instead, you will need to explicitly Lookup the field you need from the other table using a common identifier, then create a column to store the output using an Add Columns function.

I am not clear what the data type is for BLOCKED in INVENTTABLEMODULE, or how many rows you have in your data sources, but the below expression should give you a steer:

Filter(
    AddColumns(
        INVENTTABLE;
        "LookupInventModule";
        LookUp(
            INVENTTABLEMODULE;
            'Invent Table Module ID' = ID;
            BLOCKED.Value
        )
    );
    Len(ITEMID) = 7;
    StartsWith(
        ITEMGROUPID;
        "1000"
    );
    StartsWith(
        ITEMID;
        Text(Dropdown1.SelectedText.FabrikNummer)
    );
    LookupInventModule = 0
)

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[MarcoSchade]

Hi @Amik 

This is what I ended up with: 

Filter(AddColumns(INVENTTABLE;"LookupInventModule";LookUp(INVENTTABLEMODULE;ITEMID=ITEMID;BLOCKED.Value));Len(ITEMID)=7 && StartsWith( ITEMGROUPID; "1000" ) && StartsWith( ITEMID; Text(Dropdown1.SelectedText.FabrikNummer) ) && Value(LookupInventModule) = "0" )

However, I get the yellow triangle and an error about that it cannot compare number and text.

Where could it be comparing text with numbers? 

The ITEMID's are both Nvarchar (20) in the sql tables and Blocked is an int. 

Inventable has about 12.000 rows and inventtablemodule is about 37.000 rows. 

Thanks for your help. 

[Amik]

Hi @MarcoSchade ,

You are trying to compare a field wrapped in a Value function with Text.

Value(LookupInventModule) = "0" )

It should be:

Value(LookupInventModule) = 0 )

On a separate note, careful with comparing two fields with the same field name:

ITEMID=ITEMID

Your expression should still work, but consider including a RenameColumns function in your expression to make the distinction clearer.

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[MarcoSchade]

Hi @Amik 

I have now corrected this part 

Value(LookupInventModule) = 0 )

and inserted the RenameColumns function 

Filter(
     RenameColumns(INVENTTABLE;"ITEMID";"ITITEMID");
     AddColumns(
            INVENTTABLE;"LookupInventModule";LookUp(INVENTTABLEMODULE;ITEMID=ITITEMID;BLOCKED));
    Len(ITITEMID)=7 ; 
    StartsWith(ITEMGROUPID; "1000" ) ;
    StartsWith(ITITEMID; Text(Dropdown1.SelectedText.FabrikNummer) ) ; 
    Value(LookupInventModule) = 0 )

This gives the error "Boolean value expression. We expected a boolean value here in this expression". If i remove this part  

RenameColumns(INVENTTABLE;"ITEMID";"ITITEMID");

there is no error message anymore, but it does not seem to evaluate this line 

Value(LookupInventModule) = 0

and because of that i still see record where the value is both 0 and 1.

[Amik · Accepted solution]

@MarcoSchade - You have applied the function in the wrong place.

Filter(
    AddColumns(
        INVENTTABLE;
        "LookupInventModule";
        LookUp(
            RenameColumns(
                INVENTTABLEMODULE;
                "ITEMID";
                "_ITEMID"
            );
            _ITEMID = ITEMID;
            BLOCKED.Value
        )
    );
    Len(ITEMID) > 7;
    StartsWith(
        ITEMGROUPID;
        "1000"
    );
    StartsWith(
        ITEMID;
       Text(Dropdown1.SelectedText.FabrikNummer)
    );
    LookupInventModule = 0
)

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[MarcoSchade · Accepted solution]

Hi @Amik 

Thank for all your help. 

Adding just a few adjustments to you last post and then it worked. 
However i think that the SQL tables was so large that when it finally worked it took forever for it to get in the data and the app continued to lag and crash. I ended up creating a SQL view for this, which made it a lot smother. 

Filter(
    AddColumns(
        INVENTTABLE;
        "LookupInventModule";
        LookUp(
            RenameColumns(
                INVENTTABLEMODULE;
                "ITEMID";
                "_ITEMID"
            );
            _ITEMID = ITEMID;
            BLOCKED
        )
    );
    Len(ITEMID) > 7;
    StartsWith(
        ITEMGROUPID;
        "1000"
    );
    StartsWith(
        ITEMID;
       Text(Dropdown1.SelectedText.FabrikNummer)
    );
    Value(LookupInventModule) = 0
)

[Amik]

You’re welcome @MarcoSchade. Please accept the Solution I gave as the solution. Note if someone in the community ultimately solved or provided the majority of the solution to a problem, the etiquette is to mark their response as the solution.

[MarcoSchade]

Ok I will do that. 

I just thought it would be a help to the next one who read the post.