March 14, 200520 yr Author 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 ?
March 14, 200520 yr 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 ?
March 14, 200520 yr Author 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 ?
March 14, 200520 yr 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 & ", "].
March 14, 200520 yr 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 & ", "].
March 14, 200520 yr 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 & ", "].
March 14, 200520 yr Author 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-!!
March 14, 200520 yr Author 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-!!
March 14, 200520 yr Author 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-!!
March 14, 200520 yr 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.
March 14, 200520 yr 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.
March 14, 200520 yr 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.
March 15, 200520 yr 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.
March 15, 200520 yr 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.
March 15, 200520 yr 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.
March 15, 200520 yr Author 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!
March 15, 200520 yr Author 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!
March 15, 200520 yr Author 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!
Create an account or sign in to comment