Lantica User Forum - Retrieving only some subform records

(UTC)

Lantica User Forum › Lantica Discussions › Lantica Discussion › Retrieving only some subform records

(Moderator: Forum Admin)

‹ Previous Topic | Next Topic ›

Page Index Toggle

Pages: 1

Send Topic

Print

Hot Topic (More than 10 Replies)

Retrieving only some subform records (Read 1660 times)

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Retrieving only some subform records Mar 10^th, 2006 at 3:22am	Print Post
	I have a membership form similar to a Sales Order, but only want to print selected line items vs. all of them. I have a parent form with a table-view subform. There is only one record for any parent individual. The subform has many records, one field has the current year of that records, ie: 1984, 1999, 2004, etc. This year is a text value. Each sub record also has a field that has a ParentRecord ID. There may be multiple lines with the same year, those are what I want to get. ------------------------------ I need to print parent records that only show subform records for a specific year, but cannot find a way to do the retrieve. My first retrieve was for the parent individual's name, and the subform field for year=2006, only wanting to see the Parent and the four lines for 2006, but get a total of all 14 subform records for all years. My next retrieve was on the subform section only, entering 2006 in the field and entering the correct value into the ParentRecordID for the subform. Still brings up the Parent, but still brings up all 14 of the subform records. My next retrieve just enters 2006 in the year field but now I get all parents who have a subform record for 2006. I can retrieve from the subform form as standalone, but now I don't have any of the Parent information, Name, address, YTD values, contacts, etc. How is it possible to do this?


	IP Logged

Carl Underwood Senior Member Members Offline Posts: 1351 Location: New Hampshire Joined: Mar 11^th, 2003	Re: Retrieving only some subform records Reply #1 - Mar 10^th, 2006 at 4:15am	Print Post
	You could filter out the unwanted subrecords if you use PrintString programming to do your printing. But, if you are printing via "Print Form" on the command tree, I don't think you can limit which subrecords show up along with the parent record. Though, maybe Mark or Ray would have a solution in that case.

	Carl Underwood CDU Computer Consulting LLC Epsom, New Hampshire
	WWW IP Logged

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Re: Retrieving only some subform records Reply #2 - Mar 10^th, 2006 at 5:18am	Print Post
	Not using Print Form or PrintString Am using Export to create MergeLetter ascii file to use with Word. Can I maybe make special form with programming to make entire subform record invisible if year <> xxxx ? What would programming look like to do that?


	IP Logged

Hammer YaBB Administrator Lanticans Offline Fire bad. Tree pretty. Posts: 3436 Location: Ohio Joined: Nov 22^nd, 2002	Re: Retrieving only some subform records Reply #3 - Mar 10^th, 2006 at 1:09pm	Print Post
	You can loop through the subrecords in code and use File I/O to write out only those that match your criteria.

	- Hammer The plural of anecdote is not data.
	WWW IP Logged

Carl Underwood Senior Member Members Offline Posts: 1351 Location: New Hampshire Joined: Mar 11^th, 2003	Re: Retrieving only some subform records Reply #4 - Mar 10^th, 2006 at 1:11pm	Print Post
	Programming can handle that. Rather than using the export feature, use FileWrite and it's related commands. You could have code in the parent form which uses @FormFieldValue to retrieve data from the subform records, and condtionally (based on the data itself) whether or not to send it to the export file that you create with FileWrite. This would be contained in a loop that performs this on each subrecord.

	Carl Underwood CDU Computer Consulting LLC Epsom, New Hampshire
	WWW IP Logged

Carl Underwood Senior Member Members Offline Posts: 1351 Location: New Hampshire Joined: Mar 11^th, 2003	Re: Retrieving only some subform records Reply #5 - Mar 10^th, 2006 at 1:14pm	Print Post
	Ahhh! While I was composing my post, Erika posted with a much simpler way to state it.

	Carl Underwood CDU Computer Consulting LLC Epsom, New Hampshire
	WWW IP Logged

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Re: Retrieving only some subform records Reply #6 - Mar 10^th, 2006 at 4:22pm	Print Post
	So, it does sound like there is no way to bring up a form with limited subform records. So the only solution to do this with a MergeLetter is to use File I/O tools to create the data file. Lots of work to do that, maybe Version 2 will have new tools to solve that. How about an existing method of creating the merge data file with all the subrecords, and then parsing/editing the data file to remove the lines that are not needed? Any ideas about that?


	IP Logged

Bob_Hansen Senior Member Members Offline WOW, They have the Internet on computers now! Posts: 1861 Location: Salem, NH Joined: Nov 24^th, 2002	Re: Retrieving only some subform records Reply #7 - Mar 10^th, 2006 at 4:40pm	Print Post
	Quote: How about an existing method of creating the merge data file with all the subrecords, and then parsing/editing the data file to remove the lines that are not needed? Any ideas about that? If you are comfortable with RegEx tools, you might be able to do something like this: 1. Add a new first and last element to the subform records. and fill with a unique value to ID record start and end. Now do the normal export that includes all sub records. 2. Use RegEx on the exported data file to locate unwanted value between those markers, and replace with nothing. Example, start with this containing all sub records: "Parent Record elements","Recordheader", "Record1 elements","year=2005","more Record1 elements","RecordTrailer", "Recordheader", "Record2 elements","year=2006","more Record2 elements","RecordTrailer", "Recordheader", "Record3 elements","year=2005","more Record3 elements","RecordTrailer", "Recordheader", "Record4 elements","year=2006","more Record4 elements","RecordTrailer" Use Regex to look for RecordHeader followed by any data, followed by year=2005, followed by any data, followed by RecordTrailer, and replace that string with nothing. That would result with only having records for year=2006, like this: "Parent Record elements", "Recordheader", "Record2 elements","year=2006","more Record2 elements","RecordTrailer","Recordheader", "Record4 elements","year=2006","more Record4 elements","RecordTrailer" Exact RegEx syntax will very depending on what RedEx tools you are using. If you are not familiar with RegEx this may not make much sense, Which reminds me, Mark ...... I have asked about RegEx a few times over the past few years wondering if it would be available to us. Will any RegEx tools be available in Version 2.0?

	Bob Hansen Sesame Database Manager Professional Sensible Solutions Inc. Salem, NH 603-898-8223 Skype ID = sensiblesolutions
	WWW Skype/VoIP IP Logged

Ray the Reaper Global Moderator Members Lantica Support Offline The One & The Only Posts: 2482 Joined: Aug 20^th, 2003	Re: Retrieving only some subform records Reply #8 - Mar 10^th, 2006 at 4:57pm	Print Post
	If you do not know RegEx you can edit your Merge Data file in Sbasic using the String Array Commands found on pages 103-112 of the 1.1 Programming Guide. Read the File into a String using @Insert Set the String Array Separator to be @NewLine() Loop through the records Use DeleteStringArray() to remove the records that you do not want to merge Write the String Array out to the Merge Data file -Ray

	Raymond Yoxall Consulting ray.yoxall@gmail.com ryoxall@lantica.com Sesame Applications, Design and Support
	WWW IP Logged

The Cow YaBB Administrator Offline Posts: 2530 Joined: Nov 22^nd, 2002	Re: Retrieving only some subform records Reply #9 - Mar 10^th, 2006 at 5:14pm	Print Post
	Quote: Which reminds me, Mark ...... I have asked about RegEx a few times over the past few years wondering if it would be available to us. Will any RegEx tools be available in Version 2.0? Yes. There is a regular expression search option in 2.0.

	Mark Lasersohn&&Programmer&&Lantica Software, LLC
	WWW IP Logged

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Re: Retrieving only some subform records Reply #10 - Mar 10^th, 2006 at 5:18pm	Print Post
	Ray's way sounds easier than Bob's way and what is RegEx anyway?


	IP Logged

Bob_Hansen Senior Member Members Offline WOW, They have the Internet on computers now! Posts: 1861 Location: Salem, NH Joined: Nov 24^th, 2002	Re: Retrieving only some subform records Reply #11 - Mar 10^th, 2006 at 5:31pm	Print Post
	Quote: Yes. There is a regular expression search option in 2.0. Great....hope it has Replace as well as Search abilities. Also hope for POSIX and lookaround tools? Getting greedy now (hmmm greediness also?) Quote: what is RegEx anyway? RegeX is for Regular Expressions. The quick answer is to say it is an advanced method of locating and manipulating generic string patterns. I think the best reference on RegEx is "Mastering Regular Expressions",Second Edition by Jeffrey Friedl, published by O'Reilly. Was about $40 about 5 years ago. See http://en.wikipedia.org/wiki/Regex for history ond more info re RegEx

	Bob Hansen Sesame Database Manager Professional Sensible Solutions Inc. Salem, NH 603-898-8223 Skype ID = sensiblesolutions
	WWW Skype/VoIP IP Logged

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Re: Retrieving only some subform records Reply #12 - Mar 11^th, 2006 at 8:52pm	Print Post
	Thank you to everyone for your suggestions. I decided to try this one from Ray. Quote: Read the File into a String using @Insert Set the String Array Separator to be @NewLine() Loop through the records Use DeleteStringArray() to remove the records that you do not want to merge Write the String Array out to the Merge Data file I was able to follow until I had to loop and Delete Here is an abbreviated sample of normal export file. Fields from FirstName through Phone are from the Parent. This is followed by 6 fields for each subform record. Code FirstName^MiddleName^LastName^IDNumber^Address1^Address2^City^State^Zip5^Zip4^Country^Phone^ActivityDate1^Activity1^Fee1^Points1^RunningTotal1^MembershipYears1^ActivityDate2^Activity2 ^Fee2^Points2^RunningTotal2^MembershipYears2^ActivityDate3^Activity3^Fee3^Points3^RunningTotal3^MembershipYears3^ActivityDate4^Activity4^Fee4^Points4^RunningTotal4^MembershipYears4^ActivityDate5 ^Activity5^Fee5^Points5^RunningTotal5^MembershipYears5^ Because "records" are from subform then replacing separator with newline() in this file does not result in separate records to delete, it ends up with separate fields to delete. Here is a result of replacing separator with newline() Code FirstName MiddleName LastName IDNumber Address1 Address2 City State Zip5 Zip4 Country Phone ActivityDate1 Activity1 Fee1 Points1 RunningTotal1 MembershipYears1 ActivityDate2 Activity2 Fee2 Points2 RunningTotal2 MembershipYears2 ActivityDate3 Activity3 Fee3 Points3 RunningTotal3 MembershipYears3 ActivityDate4 Activity4 Fee4 Points4 RunningTotal4 MembershipYears4 ActivityDate5 Activity5 Fee5 Points5 RunningTotal5 MembershipYears5 ----------------------------------- Suppose I want to remove sub records 3 and 4 because the Fee>10. How do I loop through all of these to delete the unwanted fields. If I test for Fee>10 then I am already on third field of the six fields that I want to delete, starting at ActivityDate3. Some way of counting position then replacing in string from (current position-2) through (current position+3)? How would I do that since I already passed fields I want to delete? Sample here only shows 5 subrecords, but could be hundreds and there are many more fields than shown here. Did I do something wrong here?


	IP Logged

The Cow YaBB Administrator Offline Posts: 2530 Joined: Nov 22^nd, 2002	Re: Retrieving only some subform records Reply #13 - Mar 12^th, 2006 at 3:27am	Print Post
	How did you produce the file you are using in @insert? It looks like the output from Tom's mail merge program. He usually uses the caret as a separator.

	Mark Lasersohn&&Programmer&&Lantica Software, LLC
	WWW IP Logged

obfusc88 Full Member Offline No personal text Posts: 194 Joined: Dec 17^th, 2005	Re: Retrieving only some subform records Reply #14 - Mar 12^th, 2006 at 10:52am	Print Post
	That was a good catch. The merge data was based on an article from Inside Sesame. I forgot to look at button program for merge letter and thought it was using normal Export. But now that I look at it I can see that I am actually using File I/O, so I will have to look at the other suggestions. Hammer already suggested doing that on 3/10 but I wanted to use normal export instead of writing programming for File I/O. So I will look at that again.


	IP Logged

Page Index Toggle

Pages: 1

Send Topic

Print

‹ Previous Topic | Next Topic ›

« Board Index ‹ Board Top

Top

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

Valid XHTML

Valid CSS

Powered by Perl

Source Forge