……Marco Valli's Blog……

Internet, Tecnologia e SharePoint!!!

Il mirabolante mondo delle colonne calcolate in Sharepoint… (Parte 1)


Quest’oggi voglio mettera a diposizione dei calcoli che ho effettuato sfruttando le colonne calcolate di Sharepoint, una funzione che passa inosservata ma che può rivelare delle sorprese…

Creando un workflow mi è sortoun problema, avendo una lista di Project Manager (semplice lista con i nomi scritti a mano) utilizzata per un lookup in un’altra lista, dovevo convertire il nome scritto nello username per poterlo scrivere in un campo “Utenti i Gruppi“, tenendo conto che la policy del cliente prevedeva un nome utente composto dalla prima lettera del nome seguita dal cognome (Es. Marco Valli > “mvalli”) utilizzando la colonna calcolata e le funzioni RICERCA, CONCATENA E LUNGHEZZA sono riuscito a convertire il nome in uno username a tutti gli effetti…

=CONCATENA(“nomedominio\”;(SINISTRA([Project Manager];1));DESTRA([Project Manager];(LUNGHEZZA([Project Manager]))-(RICERCA(” “;[Project Manager];1))))

In poche parole tenendo come esempio il nome Marco Valli, ho concatenato la prima lettera a sinistra ((SINISTRA([Project Manager];1)=M) con il numero di caratteri da destra (DESTRA([Project Manager];(LUNGHEZZA([Project Manager]))-(RICERCA(” “;[Project Manager];1))=Valli) calcolato ricercando il carattere spazio di separazione tra nome e cognome ((RICERCA(” “;[Project Manager];1)=6) e sottraendolo al numero di caratteri di tutta la stringa ((LUNGHEZZA([Project Manager])=11). è bastato aggiungere un’ulteriore concatenazione per aggiungere anche il dominio e ottenere quini lo username completo..

Problema quindi apparentemente risolto!! Perchè apparentemente? perchè dopo qualche test ho notato che esistono persone con cognome composto (Es. Mario De Rossi), e il risultato è quindi un nome utente non valido (in questo caso “mde rossi”)…

Le cose ancora più problematiche erano:

  • non esiste una funzione per rimuovere arbitrariamente tutti gli spazi in una stringa, la funziona ANNULLA.SPAZI sfortunatamente si limita a rimuovere eventuali spazi all’inizio e alla fine della stringa ma non quelli in mezzo
  • ripetendo la funzione di ricerca spazi su una stringa che non ne contiene, da luogo al famigerato #VALORE! che rende di fatto inutilizzabile la formula..

La soluzione è arrivata dopo una buona oretta passata in compagnia di excel (il miglior modo di creare le formule per le colonne calcolate.. anche se bisogna fare attenzione a non utilizzare funzioni non supportate)..

E’ abbastanza complesso spiegare passo passo la formula, basta sapere che ho utilizzato un escamotage inserendo di default uno spazio in fondo alla stringa prima di ricercare l’eventuale secondo spazio e poi confrontando la posizione del carattere trovato con quella della lunghezza della stringa originale, se il numero del carattere è inferiore a quello della stringa originale allora è un secondo spazio, se è invece la posizione è oltre il numero di caratteri della stringa principale, allora vuol dire che non ci sono altri spazi nella stringa…

Più semplice a farsi che a dirsi… ecco la formula, in questo caso non c’è la concatenazione con il dominio ma quella è cosa da poco.. ;):

=SE(RICERCA(” “;CONCATENA((SINISTRA([Project Manager];1));DESTRA([Project Manager];((LUNGHEZZA([Project Manager]))-(RICERCA(” “;[Project Manager];1))));” “))<LUNGHEZZA(CONCATENA((SINISTRA([Project Manager];1));DESTRA([Project Manager];((LUNGHEZZA([Project Manager]))-(RICERCA(” “;[Project Manager];1))));” “));RIMPIAZZA(CONCATENA((SINISTRA([Project Manager];1));DESTRA([Project Manager];((LUNGHEZZA([Project Manager]))-(RICERCA(” “;[Project Manager];1)))));RICERCA(” “;CONCATENA((SINISTRA([Project Manager];1));DESTRA([Project Manager];((LUNGHEZZA([Project Manager]))-(RICERCA(” “;[Project Manager];1))))));1;””);CONCATENA((SINISTRA([Project Manager];1));DESTRA([Project Manager];((LUNGHEZZA([Project Manager]))-(RICERCA(” “;[Project Manager];1))))))

Con un colpo solo sono quindi in grado di gestire nomi con uno o due spazi (nel mio caso non avevo bisogno di andare oltre ma, con questa tecnica, sarebbe possibile eliminare un numero anche superiore di spazi, anche se la formula si complica in maniera esponenziale..)

Il tutto senza una riga di programmazione… e non è da poco!!! 😀

Enjoy!!

Annunci

4 risposte a “Il mirabolante mondo delle colonne calcolate in Sharepoint… (Parte 1)

  1. walter 18 marzo 2009 alle 19:06

    iuy

  2. Massimo 9 luglio 2009 alle 11:34

    Non sapevo della potenza delle colonne calcolate, ottimo post!

  3. Marco 19 gennaio 2011 alle 18:02

    Ma se in una colonna del sito volessi inserire il valore calcolato del numero del giorno di oggi?
    Ho provato con diverse formule del tipo
    INT(GIORNO(OGGI()))
    ma niente da fare.

    Qualcuno ha un consiglio da darmi?

    • Marco Valli 19 gennaio 2011 alle 21:26

      Utilizzare la funzione “Oggi” non è ufficialmente supportato dalle colonne calcolate di Sharepoint ma (e in SharePoint c’è spesso un ma!!) c’è un trucchetto per aggirare questa limitazione, per ottenere il risultato è sufficiente:

      1) creare una colonna di testo denominata Oggi (o Today se il sito è in inglese)
      2) creare la colonna calcolata che necessita della variabile “Oggi”, nel punto in cui serve la variabile inserire [Oggi] (come se fosse una normale colonna)
      3) una volta creata la formula salvare la colonna
      4) eliminare la colonna di testo denominata “Oggi”

      Come per magia la colonna calcolata funzionerà calcolando correttamente la variabile “Oggi” 🙂
      Unico accorgimento, ogni qualvolta si rendesse necessario modificare la formula, bisogna ri-creare la colonna “oggi” eliminandola nuovamente dopo aver effettuato la modifica…

      Spero di esserti stato d’aiuto!! 😀

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: