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

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.

I am not with you for the last part of the calc. It looks like a related field... (selfjoinon_ID::Date Log).

Bikeman17

Hi Bikeman,

You mean you cannot index it. OK. So just perform a find on 1 in that field.

There may be another solution anyway.

I went to test it on my FM and it seems to work fine.

Ugo

I don't mean I cannot index it. I simply don't understand the calc relationship.

Bikeman17

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.

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

Pupiweb,

It worked as a rocket!!

You made my day.

Bikeman17

Hi,

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

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

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 , as I now got a list of all invoices due printed at the computer start-up using MY calc (just kidding guys)

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) ...

Hi Giuseppe,

I had never used the "Copy all records" script before...and I just discovered why I couldn't get your fixed portal to work.

Thanks again.

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.

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.

Hi,

Which is faster ? Value List or Copy All records ? Italian or Canadian ?

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

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..."

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

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

Hmm...the racing Helmet.

It's Gilles VILLENEUVE's racing helmet (Canadian - Quebec) when he was driving a Ferrari (Italian) !

Think of what the dilemna can be.