Cannot compare List with Text

[TotalSolutions]

I have a Order Table and it has a Column StockItem which is a Ref to StockItem Table. In the Valid If I am puting the following formula so that Only the Stock Items that have Sub Module as Order will show up and I am using ORDERBY so it shows the Stock Items in Alphabetical Order: 

ORDERBY ( SELECT ( StockItem [SIID], [SISubModule] = “Order”, [SIName] ) )

But it gives error as "Cannot compare List with Text in ([SISubModule] = "Order")"

Please let me know how to fix it.

Thank you 😊🙏

[Suvrutt_Gurjar · Accepted solution]

Please try 

ORDERBY ( SELECT ( StockItem [SIID], ISNOTBLANK (INTERSECT([SISubModule] , SubModuleOrder[SubModuleID]))), [SIName] ) 

[Suvrutt_Gurjar]

Please try

ORDERBY ( SELECT ( StockItem [SIID], [SISubModule] = “Order”), [SIName] ) 

[TotalSolutions]

Same error as follows:

Cannot compare List with Text in ([SISubModule] = "Order")

[Suvrutt_Gurjar]

Could you please show the error and the associated column types screenshots?

Also is [SISubModule] enumlist or list type column?

[TotalSolutions]

SISubModule is Enum List and we can select multiple.

ORDERBY ( SELECT ( StockItem [SIID], [SISubModule] = “Order”), [SIName] ) 

Instead of Order I have to put the UUID: 0388FFBC-4AC7-4EA6-8AAA-FEECCA0D0974

ORDERBY ( SELECT ( StockItem [SIID], [SISubModule] = “0388FFBC-4AC7-4EA6-8AAA-FEECCA0D0974”), [SIName] ) 

[Suvrutt_Gurjar]

You have not shared the details of column [SISubModule] 

It seems to be a list of some unique values. Not sure why and how you are comparing it with the word, "Order"

If it is enumlist,  it can have many values. You are comparing it with "Order" . Are you expecting only if one value out of many is "order" , even then the corresponding [SSID] is selected?

Please elaborate.

[TotalSolutions]

Correct I am expecting only if one value out of many is "order" , even then the cirresponding [SSID] should be selected. Perfecty understood by you! 😊👏

[Suvrutt_Gurjar]

But the column values of [SISubmodule] do not have any values such as "Order" then how and from where are you expecting the word "Order" in that enumlist?

[TotalSolutions]

Instead of Order I have to put the UUID: 0388FFBC-4AC7-4EA6-8AAA-FEECCA0D0974

ORDERBY ( SELECT ( StockItem [SIID], [SISubModule] = “0388FFBC-4AC7-4EA6-8AAA-FEECCA0D0974”), [SIName] ) 

[Suvrutt_Gurjar]

Sorry that I am  unable to understand your requirement as I request you to update from where you are expecting the word "order" in the enumlist column.

[TotalSolutions]

I think attached photo will clear your doubt. If you see the Last entry in the photo has SubModuleID as: 0388FFBC-4AC7-4EA6-8AAA-FEECCA0D0974 and its Sub Module Name (SMName) is Order.

Please let me know if you have any other query. Thank you! 😊

[Suvrutt_Gurjar]

Thank you. But I am still unable to understand as none of the column names in the table you have shared , such as [SMName] , [SubmoduleID] are present in the expression you have shared. In the image you shares [SMName] column contains the word "Order" but in the expression neither that column nor any other column from the image is there.

Which table's columns are there in the image?

---

@TotalSolutions wrote:

ORDERBY ( SELECT ( StockItem [SIID], [SISubModule] = “Order”, [SIName] ) )

---

[TotalSolutions]

This is the SubModule Table.

[Suvrutt_Gurjar]

Thank you. But then I am sorry that I am unable to understand its relationship with StockItem table and the expression you have shared.

The post thread started with a simple syntactical error and it sounds that you are trying to achieve some cross table expression. However unless you could share the requirement in plain English with relevant table names and column names, I am afraid the community may not be able to help much further.

[TotalSolutions]

There is Stock Item Table with Columns: SIID, SIName, SISubModule. Also 1 Stock Item can have multiple SISubModules so I have made a SISubModule as EnumList

There is Sub Module Table with Columns: SubModuleID, SubModuleName (SMName).

Orders Table with Columns: OrderID, CustomerName, StockItemName (SIName).

So in the Orders Form Stock Item Name is a Dropdown. I want to Show only Stock Items that have Sub Module as "Order" in the Drop Down List.

I hope I have made it easier to understand.

[Suvrutt_Gurjar]

Thank you. Also please share reference relationships of these three tables. 

[TotalSolutions]

Order Form Stock Item Name (SIName) is Ref to Stock Item Table

Stock Item Form SISubModule is EnumList & Base Type is Ref. to Sub Module Table.

Sub Module Form is just regular form to enter SubModuleID & Sub Module Name.

I hope this answers your question. Thanx 😊

[Suvrutt_Gurjar]

Please try below.

Please create a slice called say  "Orders_Sub_Module" on the Sub Module Table with a slice filter expression something like [SMName]= "Order"

Then your expression for the stock item in Orders form can be something like

ORDERBY ( SELECT ( StockItem [SIID], ISNOTBLANK(INTERSECT([SISubModule] , Orders_Sub_Module[SubModuleID])), [SIName] ) )

Not tested. Please test well.

[TotalSolutions]

ORDERBY ( SELECT ( StockItem [SIID], ISNOTBLANK (INTERSECT([SISubModule] , SubModuleOrder[SubModuleID])), [SIName] ) )

Error Message: SELECT has invalid inputs

Please let me know. Thanx ‌😊‌

[Suvrutt_Gurjar · Accepted solution]

Please try 

ORDERBY ( SELECT ( StockItem [SIID], ISNOTBLANK (INTERSECT([SISubModule] , SubModuleOrder[SubModuleID]))), [SIName] ) 

[TotalSolutions]

You are a Champion at Appsheet!! You have no idea how important this was for me! God Bless you! and Thank you for being patient in understanding all the errors and fixing them!  🤗 🙏 👏 👌

Finally I did the following as per your advice:

Create a slice called  "Sub Module Order" on the Sub Module Table with a Row Filter Condition [SubModuleID] = "0388FFBC-4AC7-4EA6-8AAA-FEECCA0D0974"

In the Order Form StockItem Column add the following formula in ValidIf:

ORDERBY ( SELECT ( StockItem [SIID], ISNOTBLANK ( INTERSECT ([SISubModule], Sub Module Order [SubModuleID] ) ) ), [SIName] )

This will show only the Stock Items which have Sub Module as Order and also sort them Alphabetically.

Thank you!! 🙏 😊

[Suvrutt_Gurjar · Accepted solution]

Thank you for the update and good to know that it works. However from your latest post and the slice filter expression, you have shared, it sounds that there is only one row in the Sub Module table with "Order" as SM name, then the expression can be further optimized and you do not need a slice also. If there is ever only one row with "Order" as [SMName], then please try the following simpler expression. You do not need the slice also.

ORDERBY ( SELECT ( StockItem [SIID],   IN("0388FFBC-4AC7-4EA6-8AAA-FEECCA0D0974" , [SISubModule]) ), [SIName] )

[TotalSolutions]

Works perfect! Short and Sweet!! Another Masterstroke from you!! Thank You!! 😊 🙏

[Sirfyaad]

ORDERBY ( SELECT ( StockItem [SIID], [SISubModule] = “Order”, [SIName] ) )

The ORDERBY function expects a list of values as the first argument and a column or expression to sort the list by as the second argument. In your case, it seems that you're trying to compare a list with a text value, which is causing the error.

To fix this error, you need to ensure that the first argument of the ORDERBY function is a list of values. Here's an example of how you can modify your expression:

ORDERBY( SELECT( StockItem[SIID], AND([SISubModule] = "Order", [SIName]) ), [SIName] )

In this example, the SELECT expression retrieves the SIID values from the StockItem table where the SISubModule is "Order" and the SIName is not empty. Then, the ORDERBY function is used to sort the resulting list of SIID values by the SIName column.

Make sure to adjust the column names and conditions according to your specific data model.

[TotalSolutions]

Thank you for your response 😊

I put your expression. Attached error photo.