SOLVED

Filter column lookup with JSON

%3CLINGO-SUB%20id%3D%22lingo-sub-2055894%22%20slang%3D%22en-US%22%3EFilter%20column%20lookup%20with%20JSON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2055894%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20I%20need%20help%20how%20to%20filter%20with%20lookup%20column%20which%20is%20on%20first%20list%20(Marketing%20Catalog)%20using%20JSON%20Script%2C%20and%20on%20second%20list%20(catalog%20order)%20using%20lookup%20column%20to%20join%20it.%3C%2FP%3E%3CP%3EAnd%20i%20want%20only%20status%20active%20is%20only%20show%20on%20second%20list%20(Catalog%20Order)%2C%20can%20anyone%20help%20me%3F%3C%2FP%3E%3CP%3EI%20attach%20my%20picture%20for%20more%20detail%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Active%20Catalog.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246036i2E1322855C0BF3FE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Active%20Catalog.png%22%20alt%3D%22Active%20Catalog.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2055894%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2056247%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20column%20lookup%20with%20JSON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2056247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F919315%22%20target%3D%22_blank%22%3E%40lucasignatius%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20cannot%20filter%20list%20views%20using%20JSON%20formatting.%20You%20need%20to%20edit%20your%20list%20view%20and%20apply%20necessary%20filters%20based%20on%20lookup%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20this%20documentation%3A%26nbsp%3B%3CA%20title%3D%22Use%20filtering%20to%20modify%20a%20SharePoint%20view%22%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fuse-filtering-to-modify-a-sharepoint-view-3d8efc52-0808-4731-8f9b-3dfaeacea3d4%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EUse%20filtering%20to%20modify%20a%20SharePoint%20view%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20for%20your%20case%2C%20In%20order%20to%20filter%20the%202nd%20list%20based%20on%20Status%20column%2C%20you%20should%20have%20the%20data%20(Active%2FNon%20Active)%20%3CSTRONG%3Estored%3C%2FSTRONG%3E%20in%20column%20(Status)%2C%20which%20is%20not%20possible%20using%20JSON%20formatting.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EWorkaround%3C%2FSTRONG%3E%3A%3C%2FP%3E%3COL%3E%3CLI%3EGo%20to%202nd%20list%20settings.%20In%20lookup%20column%20settings%2C%20select%20StartDate%20and%20EndDate%20under%20%22Add%20a%20column%20to%20show%20each%20of%20these%20additional%20fields%22%20section.%3C%2FLI%3E%3CLI%3EAdd%20necessary%20filter%20conditions%20based%20on%20StartDate%20and%20EndDate%20(Use%20%5BToday%5D%20to%20get%20today's%20date%20in%20filter%20conditions).%3C%2FLI%3E%3C%2FOL%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%20%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20to%20solve%20your%20issue.%20This%20will%20help%20others%20to%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20post%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3ELike%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2063448%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20column%20lookup%20with%20JSON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2063448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373269%22%20target%3D%22_blank%22%3E%40ganeshsanap%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20can%20you%20give%20me%20more%20detail%20with%20screenshoot%20because%20I've%20problem%20when%20add%20%5BTODAY%5D%20on%20StartDate%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi everyone, I need help how to filter with lookup column which is on first list (Marketing Catalog) using JSON Script, and on second list (catalog order) using lookup column to join it.

And i want only status active is only show on second list (Catalog Order), can anyone help me?

I attach my picture for more detail

Thanks Active Catalog.png

3 Replies
best response confirmed by Juan Carlos González Martín (MVP)
Solution

@lucasignatius 

 

You cannot filter list views using JSON formatting. You need to edit your list view and apply necessary filters based on lookup column.

 

Check this documentation: Use filtering to modify a SharePoint view 

 

But for your case, In order to filter the 2nd list based on Status column, you should have the data (Active/Non Active) stored in column (Status), which is not possible using JSON formatting. 

 

Workaround:

  1. Go to 2nd list settings. In lookup column settings, select StartDate and EndDate under "Add a column to show each of these additional fields" section.
  2. Add necessary filter conditions based on StartDate and EndDate (Use [Today] to get today's date in filter conditions).

Please click Mark as Best Response if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

 

@ganeshsanap 

Hi, can you give me more detail with screenshoot because I've problem when add [TODAY] on StartDate

Thanks for your help

@lucasignatius 

 

Add list view filters something like below:

View filters.PNG


If the post is useful, please consider giving it Like.