Pattern Count not working through a relationship

[Steven Cappiello]

I have a table APPLICANTS related to a T.O. of itself MULT. APPLICATIONS

The relationship finds applicants who have applied to different departments at my company

by EMAIL_ADDRESS = EMAIL_ADDRESS and APPLICANT_ID not equal to APPLICANT_ID

I'd like to look through this relationship with a PatternCount to find out how many other departments

are still considering the person.

I tried to define this field in the APPLICANTS table:

Still_in_review_by_other_departments [unstored, number] =

PatternCount (MULT. APPLICATIONS::Review_Status; "In Review")

The calculation only returns ones and zeros -- I'm expecting results more like this: (0,0,0,1,2,0,3,1,2,4,1,0,3)

Any idea what the source of my problem might be ?

[Steven Cappiello]

I have a table APPLICANTS related to a T.O. of itself MULT. APPLICATIONS

The relationship finds applicants who have applied to different departments at my company

by EMAIL_ADDRESS = EMAIL_ADDRESS and APPLICANT_ID not equal to APPLICANT_ID

I'd like to look through this relationship with a PatternCount to find out how many other departments

are still considering the person.

I tried to define this field in the APPLICANTS table:

Still_in_review_by_other_departments [unstored, number] =

PatternCount (MULT. APPLICATIONS::Review_Status; "In Review")

The calculation only returns ones and zeros -- I'm expecting results more like this: (0,0,0,1,2,0,3,1,2,4,1,0,3)

Any idea what the source of my problem might be ?

[Steven Cappiello]

I have a table APPLICANTS related to a T.O. of itself MULT. APPLICATIONS

The relationship finds applicants who have applied to different departments at my company

by EMAIL_ADDRESS = EMAIL_ADDRESS and APPLICANT_ID not equal to APPLICANT_ID

I'd like to look through this relationship with a PatternCount to find out how many other departments

are still considering the person.

I tried to define this field in the APPLICANTS table:

Still_in_review_by_other_departments [unstored, number] =

PatternCount (MULT. APPLICATIONS::Review_Status; "In Review")

The calculation only returns ones and zeros -- I'm expecting results more like this: (0,0,0,1,2,0,3,1,2,4,1,0,3)

Any idea what the source of my problem might be ?

[-Queue-]

MULT. APPLICATIONS::Review_Status only refers to the first related record's Review_Status. You need to make a value list based on the related field (and starting from the APPLICANTS table) if you want to search all related Review_Status values. I would include the serial number in the value list also, so that it doesn't return each value only once.

An unstored calculation of PatternCount( ValueListItems( Get(FileName); "yourValueList" ); "in review" ) will then return the total number of related records containing 'in review' in their Review_Status.

If you want a result like 0, 0, 0, 1, 2, 0, ..., you'll have to Go to Related Records [show only related], loop through them, sorted by department, track how many results there are for each department in a global, then append the global to the parent's field using Set Field [APPLICANTS::PCField; APPLICANTS::PCField & global & ", "].

[-Queue-]

MULT. APPLICATIONS::Review_Status only refers to the first related record's Review_Status. You need to make a value list based on the related field (and starting from the APPLICANTS table) if you want to search all related Review_Status values. I would include the serial number in the value list also, so that it doesn't return each value only once.

An unstored calculation of PatternCount( ValueListItems( Get(FileName); "yourValueList" ); "in review" ) will then return the total number of related records containing 'in review' in their Review_Status.

If you want a result like 0, 0, 0, 1, 2, 0, ..., you'll have to Go to Related Records [show only related], loop through them, sorted by department, track how many results there are for each department in a global, then append the global to the parent's field using Set Field [APPLICANTS::PCField; APPLICANTS::PCField & global & ", "].

[-Queue-]

MULT. APPLICATIONS::Review_Status only refers to the first related record's Review_Status. You need to make a value list based on the related field (and starting from the APPLICANTS table) if you want to search all related Review_Status values. I would include the serial number in the value list also, so that it doesn't return each value only once.

An unstored calculation of PatternCount( ValueListItems( Get(FileName); "yourValueList" ); "in review" ) will then return the total number of related records containing 'in review' in their Review_Status.

If you want a result like 0, 0, 0, 1, 2, 0, ..., you'll have to Go to Related Records [show only related], loop through them, sorted by department, track how many results there are for each department in a global, then append the global to the parent's field using Set Field [APPLICANTS::PCField; APPLICANTS::PCField & global & ", "].

[Steven Cappiello]

Ooops.. my Review_Status field is a calculated field .. when I tried to create the value list it says the value list will not work because that field can not be indexed.

Any way around that.

Thanks for the help -Queue-!!

[Steven Cappiello]

Ooops.. my Review_Status field is a calculated field .. when I tried to create the value list it says the value list will not work because that field can not be indexed.

Any way around that.

Thanks for the help -Queue-!!

[Steven Cappiello]

Ooops.. my Review_Status field is a calculated field .. when I tried to create the value list it says the value list will not work because that field can not be indexed.

Any way around that.

Thanks for the help -Queue-!!

[-Queue-]

Does the calculation reference related fields or can you index it? You might be able get around the problem by using Evaluate, depending on what the calculation is.

[-Queue-]

Does the calculation reference related fields or can you index it? You might be able get around the problem by using Evaluate, depending on what the calculation is.

[-Queue-]

Does the calculation reference related fields or can you index it? You might be able get around the problem by using Evaluate, depending on what the calculation is.

[Ugo DI LUCA]

The calculation only returns ones and zeros -- I'm expecting results more like this: (0,0,0,1,2,0,3,1,2,4,1,0,3)

Are we reading correctly that you want a field combining all status with a comma separating each record, or is it that each value in between the commas are individual records results you were expecting to get ?

I can't think of anyway this kind of field could possibly give any interresting and usable information.

Wouldn't a Count(SelfJoin::recordID) be sufficient in each related record. A Sum of the Counts could give you a total eventually if this may help.

[Ugo DI LUCA]

The calculation only returns ones and zeros -- I'm expecting results more like this: (0,0,0,1,2,0,3,1,2,4,1,0,3)

Are we reading correctly that you want a field combining all status with a comma separating each record, or is it that each value in between the commas are individual records results you were expecting to get ?

I can't think of anyway this kind of field could possibly give any interresting and usable information.

Wouldn't a Count(SelfJoin::recordID) be sufficient in each related record. A Sum of the Counts could give you a total eventually if this may help.

[Ugo DI LUCA]

The calculation only returns ones and zeros -- I'm expecting results more like this: (0,0,0,1,2,0,3,1,2,4,1,0,3)

Are we reading correctly that you want a field combining all status with a comma separating each record, or is it that each value in between the commas are individual records results you were expecting to get ?

I can't think of anyway this kind of field could possibly give any interresting and usable information.

Wouldn't a Count(SelfJoin::recordID) be sufficient in each related record. A Sum of the Counts could give you a total eventually if this may help.

[Steven Cappiello]

I had indexing turned off.. (i hope not for a good reason!!)

i think i've got it... i found out that including a 2nd field in the value list does not add that 2nd field to the value list..

so, i had to create another field which concatenates the status and the id, and then use that field in the value list..

now it works! thanks!

[Steven Cappiello]

I had indexing turned off.. (i hope not for a good reason!!)

i think i've got it... i found out that including a 2nd field in the value list does not add that 2nd field to the value list..

so, i had to create another field which concatenates the status and the id, and then use that field in the value list..

now it works! thanks!

[Steven Cappiello]

I had indexing turned off.. (i hope not for a good reason!!)

i think i've got it... i found out that including a 2nd field in the value list does not add that 2nd field to the value list..

so, i had to create another field which concatenates the status and the id, and then use that field in the value list..

now it works! thanks!