SOLVED

JSON Column Formatting in Sharepoint based on an address field

%3CLINGO-SUB%20id%3D%22lingo-sub-1486532%22%20slang%3D%22en-US%22%3EJSON%20Column%20Formatting%20in%20Sharepoint%20based%20on%20an%20address%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1486532%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20my%20Sharepoint%20List%2C%20I%20want%20to%20hide%20a%20button%20if%20the%20user%20hasn't%20entered%20an%20address.%20The%20field%20is%20a%20Location%20field%20called%20%22Address%22%20and%20I%20cannot%20get%20the%20syntax%20correct.%20The%20JSON%20code%20extract%20is%20below%20(and%20for%20the%20avoidance%20of%20any%20doubt%2C%20the%20first%20part%20of%20the%20IF%20statement%20works%20fine.)%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20tried%20%24Address.Name%2C%20%24Address.Street%2C%20%24Address.value%20...%20I%20just%20can't%20seem%20to%20get%20it%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22visibility%22%3A%20%22%3Dif(%5B%24RTWVALIDATERef%5D%20%3D%3D%20''%20%7C%7C%20%5B%24Address%5D%20%3D%3D%20''%2C'hidden'%2C'visible')%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1486532%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-1486950%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20Column%20Formatting%20in%20Sharepoint%20based%20on%20an%20address%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1486950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F709152%22%20target%3D%22_blank%22%3E%40Geoffers%3C%2FA%3EThere%20is%20no%20direct%20way%20but%20you%20can%20always%20use%20a%20workaround.%20Like%20shown%20below%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Vikram_Samal_2-1593019753328.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F200793i6A6E5405C0DAA9BE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Vikram_Samal_2-1593019753328.png%22%20alt%3D%22Vikram_Samal_2-1593019753328.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%201%3A%20Create%20a%20Calculated%20Column%20And%20add%20the%20formula%20which%20checks%20the%20Address%20is%20empty%20or%20not.%20Like%20shown%20below%3A%20(I%20am%20making%20sure%20address%20should%20have%20a%20postal%20code)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Vikram_Samal_1-1593019602359.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F200792i374077AE82135F92%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Vikram_Samal_1-1593019602359.png%22%20alt%3D%22Vikram_Samal_1-1593019602359.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep2%3A%20Then%20use%20that%20column%20to%20check%20if%20the%20Address%20is%20present%20then%20show%20or%20hide%20the%20button%20on%20the%20basis%20of%20Calculated%20column.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7B%0A%20%20%22%24schema%22%3A%20%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fcolumn-formatting.schema.json%22%2C%0A%20%20%22elmType%22%3A%20%22span%22%2C%0A%20%20%22style%22%3A%20%7B%0A%20%20%20%20%22color%22%3A%20%22%230078d7%22%2C%0A%20%20%20%20%22background-color%22%3A%20%22White%22%2C%0A%20%20%20%20%22visibility%22%3A%20%7B%0A%20%20%20%20%20%20%22operator%22%3A%20%22%3F%22%2C%0A%20%20%20%20%20%20%22operands%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%22operator%22%3A%20%22%3D%3D%22%2C%0A%20%20%20%20%20%20%20%20%20%20%22operands%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%5B%24CheckAddress%5D%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22Present%22%0A%20%20%20%20%20%20%20%20%20%20%5D%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22visible%22%2C%0A%20%20%20%20%20%20%20%20%22hidden%22%0A%20%20%20%20%20%20%5D%0A%20%20%20%20%7D%0A%20%20%7D%2C%0A%20%20%22children%22%3A%20%5B%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%22elmType%22%3A%20%22span%22%0A%20%20%20%20%7D%2C%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%22elmType%22%3A%20%22button%22%2C%0A%20%20%20%20%20%20%22txtContent%22%3A%20%22Send%20Mail%22%2C%0A%20%20%20%20%20%20%22customRowAction%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22action%22%3A%20%22executeFlow%22%2C%0A%20%20%20%20%20%20%20%20%22actionParams%22%3A%20%22%7B%5C%22id%5C%22%3A%20%5C%22e290feff-0013-41f2-97dd-91a37fb84ea0%5C%22%7D%22%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%20%20%5D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVik%3CBR%20%2F%3EIf%20this%20post%20helps%2C%20then%20%3CSTRONG%3Eplease%20consider%20Accept%20it%20as%20the%20solution%3C%2FSTRONG%3E%20to%20help%20the%20other%20members%20find%20it%20more%20quickly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1488206%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20Column%20Formatting%20in%20Sharepoint%20based%20on%20an%20address%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1488206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394813%22%20target%3D%22_blank%22%3E%40Vikram_Samal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20this%20solution.%20It%20works%20perfectly.%20I%20have%20added%20more%20address%20fields%20to%20the%20formula%26nbsp%3B%20to%20make%20for%20a%20more%20robust%20check%20but%20the%20principle%20is%20exactly%20as%20you%20have%20outlined.%26nbsp%3B%20My%20formula%20is%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(ISBLANK(%5BAddress%3A%20Postal%20Code%5D)%2CISBLANK(%5BAddress%3A%20Street%5D)%2CISBLANK(%5BAddress%3A%20Name%5D))%2C%22Empty%22%2C%22Present%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

In my Sharepoint List, I want to hide a button if the user hasn't entered an address. The field is a Location field called "Address" and I cannot get the syntax correct. The JSON code extract is below (and for the avoidance of any doubt, the first part of the IF statement works fine.)

I have tried $Address.Name, $Address.Street, $Address.value ... I just can't seem to get it

 

"visibility": "=if([$RTWVALIDATERef] == '' || [$Address] == '','hidden','visible')"

 

Any help would be greatly appreciated.

2 Replies
best response confirmed by Geoffers (Occasional Contributor)
Solution

@GeoffersThere is no direct way but you can always use a workaround. Like shown below

Vikram_Samal_2-1593019753328.png

 

Step 1: Create a Calculated Column And add the formula which checks the Address is empty or not. Like shown below: (I am making sure address should have a postal code)

Vikram_Samal_1-1593019602359.png

 

Step2: Then use that column to check if the Address is present then show or hide the button on the basis of Calculated column.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "span",
  "style": {
    "color": "#0078d7",
    "background-color": "White",
    "visibility": {
      "operator": "?",
      "operands": [
        {
          "operator": "==",
          "operands": [
            "[$CheckAddress]",
            "Present"
          ]
        },
        "visible",
        "hidden"
      ]
    }
  },
  "children": [
    {
      "elmType": "span"
    },
    {
      "elmType": "button",
      "txtContent": "Send Mail",
      "customRowAction": {
        "action": "executeFlow",
        "actionParams": "{\"id\": \"e290feff-0013-41f2-97dd-91a37fb84ea0\"}"
      }
    }
  ]
}

 

Vik
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

@Vikram_Samal 

Thank you for this solution. It works perfectly. I have added more address fields to the formula  to make for a more robust check but the principle is exactly as you have outlined.  My formula is 

 

=IF(AND(ISBLANK([Address: Postal Code]),ISBLANK([Address: Street]),ISBLANK([Address: Name])),"Empty","Present")