Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

Inactive Members for the last 30 days


Bikeman17

This topic is 7855 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi Geeks,

I want to know members who didn't check in for the last 30 days. In other words, I am looking for records that do not exist. Each time a member ckecks in, a log is created. This logs hold this information: member id, name, date, time and a counter that displays "1" automatically. I found half of the solution but I want to get rid of duplicates.

It looks like this:

Copy (Select, Last 30 days) --> calculation field

Enter Find Mode

Insert Text (Select, "Date", "<="

Paste (Date)

Perform Find

Show Omitted

This script works as long the member didn't check in the past 30 days. To narrow the search, I need to get rid of the duplicates (members who checked in before and during the past 30 days). Do you see my problem?

Bikeman17

Link to comment
Share on other sites

Hi,

I would just use a matchcalc = Case(Status(CurrentDate) > (Last (selfjoinon_ID::Date Log) +30), 1,0)

Then use another selfjoin using a record with 1 at left side and matchcalc at right.

Not sure of my calc though, but sure it's kind the way to do it.

Link to comment
Share on other sites

Oh OK,

I thought you were OK with self relationship. Create a relationship from your file to your file based on the Member_ID. This way, the LAST funtion will return the Last date for this member, and of course all calcs would be different for any member, depending on its own last log date.

In fact you were right for the last part using constant = 1 at left and the calculation at right, because, as it referred to a relationship, this calc cannot be indexed.

It surely would be more dynamic to have this list in a portal rather than make a find. If you really need it, you could use a start-up looping script that would index this calc record after record on a day to day basis.

Check the Article section for a good article on indexing a non indexed calc if you need it.

Link to comment
Share on other sites

I think you need to go relational.

You have a Log.fp5 file, one record per entry

Make a Members.fp5 file, one record per person

In log find log entries for the past 30 days:

Enter Find Mode

Insert Calculated Result (DateToText(Today) & "..." & DateToText(Today-30)

Perform Find

Then make a global text field gID

Put in it the MemberID of the found records:

Go to layout [a layout with only MemberID in it]

Copy All Records

Go to layout [a layout with gID in it]

Paste (gID)

Isolate members who have logged in the past 30 days:

Go to related record (Show, Relationship gID->MemberID in Members.fp5)

A Show Omitted step (in Members.fp5) will tell you who has NOT logged in the past 30 days

HTH

Link to comment
Share on other sites

Hi,

This is certainly one of the exception for the use of Copy/Paste functions in scripts.

I've seen it somewhere else (fixedportal ? wink.gif )...

If Bikeman wants to make a report on a date range (not always 30 days), it sure would use this. If not, I still think the calculated field is the best for him.

Bikeman, you also made my day with your question smile.gif, as I now got a list of all invoices due printed at the computer start-up using MY calc (just kidding guys)

Link to comment
Share on other sites

Ciao Ugo

Actually I usually opted for a looping script putting the IDs in the global (because it's not layout-dependent and doesn't require that the user has export privileges) but I discovered it's much slower (5 times slower) ...

Link to comment
Share on other sites

Often, but not always, you can replace the "Copy all records" and "Paste" steps with a Set Field[gField, ValueListItems("myFile", "A_Related_Valuelist")]. This works when you can select the desired records with a relationship.

Link to comment
Share on other sites

The only gotcha with the "ValueListItems" function is that it "ignors" dulplicate entries. That is, if a value appears twice in the the related records it only apprears once in the string returned by the function.

Link to comment
Share on other sites

Since the Italian is wearing a racing helmet, my guess is Italian. laugh.gif

Actually another limitation of the ValueListItems trick besides the one Vaughan mentioned, is when the field referenced in the list contains very long entries and/or carriage returns. These all affect indexing and therefore the contents of the valuelist. That's why I said "Often, but not always..." smile.gif

However, if you limit this trick to using a valuelist from something like a unique key field, I can't think of any problems.

Link to comment
Share on other sites

Ciao Ugo

If you can use the ValueListItems function go for it, don't use any loop or Copy All records

The procedure I mentioned is useful when dealing with a found set created by a search and/or omissions, when relationships cannot be used since they'd reference a different set of records

Copy All records has been reported to be 5 times faster than the looping script, which in turn has the advantages I mentioned: doesn't require a specific layout and doesn't require that the user has export privileges (a very well hidden requirement !)

Link to comment
Share on other sites

This topic is 7855 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

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