Skip to content
View in the app

A better way to browse. Learn more.

FMForums.com

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

Pattern Count not working through a relationship

Featured Replies

  • 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 ?

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 ?

  • 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 ?

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 & ", "].

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 & ", "].

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 & ", "].

  • 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-!!

  • 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-!!

  • 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-!!

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.

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.

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.

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. crazy.gif

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. crazy.gif

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. crazy.gif

  • 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!

  • 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!

  • 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

Important Information

By using this site, you agree to our Terms of Use.

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.