Bikeman17 Posted March 28, 2003 Share Posted March 28, 2003 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 More sharing options...
Ugo DI LUCA Posted March 28, 2003 Share Posted March 28, 2003 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 More sharing options...
Bikeman17 Posted March 28, 2003 Author Share Posted March 28, 2003 I am not with you for the last part of the calc. It looks like a related field... (selfjoinon_ID::Date Log). Bikeman17 Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted March 28, 2003 Share Posted March 28, 2003 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. Link to comment Share on other sites More sharing options...
Bikeman17 Posted March 28, 2003 Author Share Posted March 28, 2003 Ugo I don't mean I cannot index it. I simply don't understand the calc relationship. Bikeman17 Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted March 28, 2003 Share Posted March 28, 2003 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 More sharing options...
Pupiweb Posted March 28, 2003 Share Posted March 28, 2003 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 More sharing options...
Bikeman17 Posted March 28, 2003 Author Share Posted March 28, 2003 Pupiweb, It worked as a rocket!! You made my day. Bikeman17 Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted March 28, 2003 Share Posted March 28, 2003 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) Link to comment Share on other sites More sharing options...
Pupiweb Posted March 31, 2003 Share Posted March 31, 2003 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 More sharing options...
Ugo DI LUCA Posted April 1, 2003 Share Posted April 1, 2003 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. Link to comment Share on other sites More sharing options...
BobWeaver Posted April 1, 2003 Share Posted April 1, 2003 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 More sharing options...
Vaughan Posted April 1, 2003 Share Posted April 1, 2003 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 More sharing options...
Ugo DI LUCA Posted April 1, 2003 Share Posted April 1, 2003 Hi, Which is faster ? Value List or Copy All records ? Italian or Canadian ? Link to comment Share on other sites More sharing options...
BobWeaver Posted April 2, 2003 Share Posted April 2, 2003 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. Link to comment Share on other sites More sharing options...
Pupiweb Posted April 2, 2003 Share Posted April 2, 2003 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 More sharing options...
Ugo DI LUCA Posted April 2, 2003 Share Posted April 2, 2003 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now