Gossamer Forum
Home : Products : DBMan : Customization :

Pulling a field from one record to another

Quote Reply
Pulling a field from one record to another
Is it possible to pull the value of a field in one record to another field in another record (same database)? Let me explain a little bit what the setup is. I've created a weight watchers daily journal to allow users to record what they ate and the points associated with the food. (WW uses a points system, and you get a range of points per day.) I have it set so the user may view only his own records. The DB tracking is set to ID; however, as a side comment, I'd love to know also if there's a way to limit each user to only one entry per date. I didn't set date as the track because there will be multiple entries for each date, but should only be one per user. Anyway, back on track...I've used javascript to get the journal to add up the points per meal and for the day and, based on the points range selected, to fill in the points available, then finally to subtract points spent from points available, resulting in a points saved field. What I would like to do is somehow have that "points saved" field from today pull in to tomorrow's "banked points" field. Is this even possible? If so, how would I go about doing this? It will have to be dependent on the userid and the date, as well.

Thanks!

Melanie
Quote Reply
Re: Pulling a field from one record to another In reply to
What I have in mind will require that you use the ID method that I proposed earlier. It also will require you to replace sub get_date with the subroutine listed at http://www.jpdeni.com/dbman/Mods/changes.txt so that you can find out what yesterday's date is.

I'll start with just looking at yesterday's records. We might be able to look at previous day's records, too, but I think this would be a start.

In db.cgi, sub get_defaults, before

return %default;

add
Code:
$yesterday = &get_date(time()-86400);
%old = &get_record($db_userid.$yesterday);
if (%old) {
$default{'banked points'} = $old{'points saved'};
}
else {
$default{'banked points'} = "0";
}

Be sure to change banked points and points saved to match the names of the fields in your database.


------------------
JPD





Quote Reply
Re: Pulling a field from one record to another In reply to
It works once there's an old entry with the correct ID (userID plus date)! Thanks a lot! I appreciate it.

Melanie
Quote Reply
Re: Pulling a field from one record to another In reply to
No problem. Smile

I don't think I would have thought of this solution if you hadn't mentioned your side issue of making sure each user only added one record per day. It all comes together really nicely.

I also wanted to tell you that I appreciate the way you explained your problem. It makes it a lot easier to give answers when I don't have to spend 10 posts figuring out what the problem is. Smile


------------------
JPD





Quote Reply
Re: Pulling a field from one record to another In reply to
Melanie, you could prevent a user from entering more than one record per day.

Let's say your userid field is UserID, your date field is Date, and your $db_key field is ID. (If you have them named anything different, you'll need to change the code below to match your names.) Set $db_key_track = 0; in your .cfg file.

In sub add_record, just after

($auth_user_field >= 0) and ($in{$db_cols[$auth_user_field]} = $db_userid);

add

Code:
$in{'ID'} = $in{'UserID'} . $in{'Date'};

So, if my userid was "JPDeni" and I added a record today, the ID for my record would be
JPDeni26-Sep-1999. If I tried to add another record today, I would get a "duplicate key error."

That question was much easier to answer than your main one. Smile

Would you only be pulling the "banked" points from the previous day? For example, if I added a record today, but I hadn't added one yesterday, what would happen? No "banked" points? Or would you want to go through the previous days' records to find my "banked" points?


------------------
JPD





Quote Reply
Re: Pulling a field from one record to another In reply to
Thanks for the instructions on limiting the user to one entry per date. I'll try it out today.

As for your question:

Quote:
Would you only be pulling the "banked" points from the previous day? For example, if I added a record today, but I hadn't added one yesterday, what would happen? No "banked" points? Or would you want to go through the previous days' records to find my "banked" points?

I also have a field called "Week" where the user can enter a numerical value (1 for week one, 2 for week two, and so forth). Depending on what day the user weighs in, the week for the user may start on different days. So...I suppose if a person didn't enter a journal today, I would like tomorrow's to pull from the most recent journal within the corresponding "week". If that's not possible, then I would just as soon have it fill in a 0.

Melanie