(UTC)
  Lantica User Forum › Lantica Discussions › Lantica Discussion › Retrieve from previous retrieve
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Normal Topic Retrieve from previous retrieve (Read 617 times)
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Retrieve from previous retrieve
Jul 3rd, 2007 at 2:41am
Print Post Print Post  
I think I know what to do but need help to do the retrieving. I want to add a new record but get data from the last record added for the same individual.  Each individual has a unique Employee ID and a field for the date the record was created.  I want to show some information from the last record on the new record being added, and maybe use some of it to make initial values on the new record.  So I think when I add the new record I can retrieve all the records for the individual Employee ID.  Then I need to retrieve the newest date from that set of records.  That should give me one record, and from that I can get the information I need.  But I don't know how to "Do a XLU on the record with the newest date added for Employee ABC".  Can I use EmployeeID in the ID field and MAX in the Date Added field in a program statement somehow?  Is there an automatice function to do that now?
  
Back to top
 
IP Logged
 
Carl Underwood
Senior Member
Members
*****
Offline



Posts: 1351
Location: New Hampshire
Joined: Mar 11th, 2003
Re: Retrieve from previous retrieve
Reply #1 - Jul 3rd, 2007 at 3:16am
Print Post Print Post  
I am doing something similar to what you want to do. Here is a sample of how I am finding the most recent Invoice record, and copying some of its data into a new record.

Beware though, that this exact code can be problematic if the data you are retrieving has semicolons in it. If your data does contain semicolons, you will need each record to have a unique Record ID, and I will need to give you another version of this code. Let me know if you need to see it.

Code
Select All
vStr = @XLookupSourceListAll(@Fn, vPhone, "Invoice!Phone",
"InvDate;Invoice No;Name;Attn;Address1;Address2;City;State;Zip Code;Memo;Note;Invoice Amt;Type of Job;Done By")
//  1   |    2     | 3  | 4  |   5    |   6    | 7  |  8  |   9    | 10 | 11 |    12     |    13     |  14
if Not @Error
	{
	SetStringArraySeparator(@NewLine())		// Consider each line a string entry.
	vStr = @SortStringArray(vStr, 0)		// Sort the strings by ASCII (sorts by date, since that is the first element).
	vCount = @CountStringArray(vStr)		// Count the strings.
	vStr = @AccessStringArray(vStr, vCount)		// Get the last string.
	RestoreStringArraySeparator()			// Restore the separator to the default ";".
	//vStr = @AccessStringArray(vStr, 2)		// vStr now contains the Invoice No.
	Name		= @AccessStringArray(vStr, 3)
	Attn		= @AccessStringArray(vStr, 4)
	Address1	= @AccessStringArray(vStr, 5)
	Address2	= @AccessStringArray(vStr, 6)
	City		= @AccessStringArray(vStr, 7)
	State		= @AccessStringArray(vStr, 8)
	Zip Code	= @AccessStringArray(vStr, 9)
	Memo		= @AccessStringArray(vStr, 10)
	Note		= @AccessStringArray(vStr, 11)
	}
  

Carl Underwood
CDU Computer Consulting LLC
Epsom, New Hampshire
Back to top
WWW WWW  
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Retrieve from previous retrieve
Reply #2 - Jul 3rd, 2007 at 3:23am
Print Post Print Post  
For Carl Underwood.  Thank you for your example.  I am not sure what you are doing yet but I will try to figure this out in the next few days. I don't have any seemicolons so that should not be a problem.
  
Back to top
 
IP Logged
 
Bob_Hansen
Senior Member
Members
*****
Offline


WOW, They have the Internet
on computers now!

Posts: 1861
Location: Salem, NH
Joined: Nov 24th, 2002
Re: Retrieve from previous retrieve
Reply #3 - Jul 3rd, 2007 at 3:26am
Print Post Print Post  
I think you can get all records with the same ID, sort the list in descending order by the date, and then just use the first record in the result set. 

I will have to check some older applications for sample code.
  


Bob Hansen
Sesame Database Manager Professional
Sensible Solutions Inc.
Salem, NH
603-898-8223
Skype ID = sensiblesolutions
Back to top
WWW WWW Skype/VoIP Skype/VoIP  
IP Logged
 
Carl Underwood
Senior Member
Members
*****
Offline



Posts: 1351
Location: New Hampshire
Joined: Mar 11th, 2003
Re: Retrieve from previous retrieve
Reply #4 - Jul 3rd, 2007 at 1:41pm
Print Post Print Post  
obfusc88 wrote on Jul 3rd, 2007 at 3:23am:
I am not sure what you are doing yet but I will try to figure this out in the next few days.

The code basically does this:

1. @XLookupSourceListAll() gathers all previous records in the database that have the same phone number as the current new record, and brings back the data from each external element that's named in the semicolon-delimited list, between the last set of quotes.

2. If there's no error (meaning it found a match), sort the string array. This will sort primarily by the first element in the array, which happens to be the date.

3. Now that the records in the array are sorted by date, we can simply pluck the last record from the array to find the most recent one. So, I use @CountStringArray() to find out how may records are in the array.

4. @AccessStringArray() uses the result from the previous step (which is now in the vCount variable) to know which record is the last one in the array. It sets vStr to contain only that last record.

5. Each of the element values can now be extracted with @AccessStringArray(), using a number which represents the position of the desired element, as it appeared in the element list in @XLookupSourceListAll().

In order to keep is simple, I left out a few other steps in between these that change the StringArraySeparator. But, this should give you a the general idea of the code sample.
  

Carl Underwood
CDU Computer Consulting LLC
Epsom, New Hampshire
Back to top
WWW WWW  
IP Logged
 
obfusc88
Full Member
***
Offline


No personal text

Posts: 194
Joined: Dec 17th, 2005
Re: Retrieve from previous retrieve
Reply #5 - Jul 9th, 2007 at 5:42pm
Print Post Print Post  
I am just back from vacation and did not get a chance to try this, but it does sound like what I am trying to do.  I think I will be able to try this out sometime this week.
  
Back to top
 
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge