list compare

[distantblue]

Just a question to clarify if something should work

I have several sheets in Smartsheet and per the limitations the company has been archiving the sheets once they get full.

Creating this app I am needing to compare things in all of these sheets. The master sheet of items remaining is also in Smartsheet's. The Techs in the field use the smart sheets so for now I need to compare what the techs input into the daily sheet(it will eventually get archived when full)  to the Master.

I am using something ike this, it is not working as expected. can someone clarify if the code below should work. It is green in the editor but I think it only acts on rows not columns or vice versa. regardless the results to <> is not working with this code.

AND(

Exchange Daily Reporting[Old Meter ERT#] <> MasterWorkListFirst20k[Old Meter ERT#],

Exchange 2 Daily[Old Meter Number] <> MasterWorkListFirst20k[Old Meter ERT#],

Exchange 3 Daily[Old Meter Number] <> MasterWorkListFirst20k[Old Meter Number],

Exchange 4 Daily[Old Meter Number] <> MasterWorkListFirst20k[Old Meter Number],

Exchange 5 Daily[Meter Work Order Number] <> MasterWorkListFirst20k[Meter Work Order Number]

)

Thank in advance

[Marc_Dillon]

---

@distantblue wrote:

it is not working as expected

---

How do you expect it to be working? In other words, what is the intended result, or function of this expression? And where are you using it?

[distantblue]

thank you

some context.  when the tech puts in Old Meter Number or ERT i was hoping to evaluate if this item was listed in the MasterWorkListFirst20k. if the item existed in both list. aka the job got done today it will now have the status marked as done else it will continue living as assigned status. 

When I use the code above in a slice or in a formula in a column, the status does not get set as done when items match , as if the code is not doing what I expect. 

I will continue to explain if other questions. Thank you

[Marc_Dillon]

You don't want to compare 2 lists to each other, you want to compare a single value to a list. Try IN()

https://help.appsheet.com/en/articles/2357277-in

[distantblue]

thank you. I have been messing with filter and in with limited results. I will spend a few hours on this today and post my results. thank you for the direction.

[distantblue]

Marc

I spent some time on in() and subtracting list. 

A couple of thoughts/ questions/ observations

I have had the best results with ,

list(MATCH TABLE[METERNUM]-FINAL LIST OF UNFINISHED INSTALLS RND BREAKOUT 2[METERNUM])

this will create accurate results when I create a new record. I need it to be viewable in a table without having to do an add. I have not figured that out.

I need to do a subtract from 6 archive list and generate a list of items that are not in any of those list. For now more archives will happen aka more than 6. I am changing how that works but for now I need to pull out only items in one master list that does not exist in any of the archive tables. I think I can do that with multiple subtracts.

This is also creating the same results but taking out any possible dups.

UNIQUE(list(MATCH TABLE[METERNUM]-FINAL LIST OF UNFINISHED INSTALLS RND BREAKOUT 2[METERNUM]))

When I am using IN for a y/n

IN(list(RND MATCH TABLE[METERNUM]), LIST(RND MATCH TABLE 2[METERNUM]))

I am using the same list just to see if it is working and it still spits out a no even tho they are the same list with a few small differences to test the subtract from above..  Perhaps it is only check 1 and not all, I am unsure

[Marc_Dillon]

list(MATCH TABLE[METERNUM]-FINAL LIST OF UNFINISHED INSTALLS RND BREAKOUT 2[METERNUM]

---

Remove the LIST() from around that, and all other places that you used it from the above post. You are actually creating a list of lists by doing that, which could easily cause issues further down the road. Expressions of the form table[column] already return a list, so does list subtraction.

https://help.appsheet.com/en/articles/4575739-list-from-table-column-reference

https://help.appsheet.com/en/articles/4575802-list-subtraction

this will create accurate results when I create a new record. I need it to be viewable in a table without having to do an add. I have not figured that out

---

App Formulas on real columns only update when the record is edited. Virtual Columns update on every sync.

IN(list(RND MATCH TABLE[METERNUM]), LIST(RND MATCH TABLE 2[METERNUM]))

---

This is not correct usage of IN(). The first parameter must be a single item. Re-read the article I linked previously.

[distantblue]

thank you, one quick clarification while I work through this

a virtual column should display subtract() results without having to do an add? aka display in a table without having to hit +

in regards to the in()

IN(needle, haystack)

• needle - Any value to be found. The value's type must be compatible with that of the items of the search list (haystack).
• haystack - A list of items (EnumList or List) to be searched. The type of items must be compatible with that of the search target (needle).

this is for a slice. i think this is closer to correct.  the hay stack can be a list. is the needle  searching the entire list or just one. should this return a list or just the first item found. 

IN([Meter Work Order Number], LIST(MASTERWORKLISTFIRST20K[Meter Work Order Number], MASTERWORKLISTFIRST20K[Meter Work Order Number]))

i have the same list in both value 1 and value 2. That is also something I am unclear how to use the values correctly.