How to combine different statuses into one field

HegeS — Fri, 29 Oct 2021 17:16:06 GMT

I work with contracts that are sent to external users for digital signing. One document must be signed by 2 people, one "leader", one "member"

I want a table that shows all documents that are being processed and detect the person that has not signed.

The table is similar to:

Left join (DOCUMENT)

LOAD

MEMBER,

LEADER,

STATUS

IF(WILDMATCH(STATUS,'*SIGNED*')=1,1,0) AS SIGNED.1.0,

FROM

BASE

(qvd);

I need one field that tells me if the member has signed, or the leader has signed or if none has signed.

Ive tried multiple things, but they end up giving me duplicates of the document, with one row for the member and another for the leader

Example:

Left join (Document)

LOAD ID,

if(len(trim([LEADER]))>0 and SIGNED.1.0=1 ,'LEADER',
if(len(trim(MEMBER))>0 and SIGNED.1.0=1 ,'MEMBER',
if((len(trim(LEADER))>0 and SIGNED.1.0=0) AND (len(trim(MEMBER))>0 and SIGNED.1.0=0),'NONE'))) as WHO.SIGNED

Resident Document;

Or master item:

=if(len(trim(LEADER))>0 and [SIGNED.1.0]=1 ,'YES')

This gives duplicates and if I try to use the "Include null values" it removes all the documents where the MEMBER has signed. I need both...

Anyone?

Re: How to combine different statuses into one field

rubenmarin — Sun, 31 Oct 2021 07:46:30 GMT

Hi, you need to detect where the duplicates comes, maybe it's in the 'BASE' table where there are more than one record for each ID, in that case you can do a previous load to keep only one row by ID, somethinglike:

BASE_JOINED: LOAD ID, MEMBER, MEMBER_STATUS, IF(WILDMATCH(STATUS,'*SIGNED*')=1,1,0) AS MEMBER_SIGNED.1.0, FROM BASE(qvd) WHERE not IsNull(MEMBER); Left Join(BASE_JOINED) LOAD ID, LEADER, LEADER_STATUS, IF(WILDMATCH(STATUS,'*SIGNED*')=1,1,0) AS LEADER_SIGNED.1.0, FROM BASE(qvd) WHERE not IsNull(LEADER);

If each ID has more than one MEMBER or LEADER you will need some extra logic to avoid duplicates, maybe just addind a composite key by ID and MEMBER or LEADER and use Exists() to avoid loading duplicates.

topic How to combine different statuses into one field in App Development

How to combine different statuses into one field

Re: How to combine different statuses into one field