Modify Line by Previous

[LaRetta]

Hi Everyone!

Oh, help please. Weekend deadline project!

This one has me stimied and I just stare at it and can't decide how to attack it, whether script, relationship and/or calc and how to even begin.

Old DOS conversion data. Invoice LineItems unsorted in exact sequence I need. Fields: LineItemID (text, unique), Invoice# (text, not unique), ProductCode (text), TransCode (text), UnitPrice (number), ExtendedPrice (number), Quantity (number).

If a Customer ordered 6 products, we many times gave them one (or more) free (called Buyer Review, Free Product, Replacement Product or Sample). In PeachTree, we had to list 7 products sold then add a credit line and minus the cost of the free products back out.

What I need is this (and I've uploaded a sample of the two types of data) displayed in the Invoice portal:

I need to take those FREE items and determine the ProductCode and set the ProductCode field accordingly. I also need to change its Unit Price to a zero and change its Qty from zero to ?. Then on the previous 'matching' product, I need to minus (or plus) Qty by that number and minus (or plus) the ExtendedPrice by that same amount.

So if the item directly above is from the same Invoice# and also has a ProductCode (not IsEmpty) and the UnitPrice of the free product is equal to the previous line (or any multiple of it), I need to adjust it.

I need to end up with those FREE products indicating the product they were, as if they were sold for no cost.

The same applies even if it is a return. So the previous product might be a minus Qty and price.

The example shows the TransCode in red and the first example is a sale and the second example is a return.

Assign Product Code

The reason I want to keep the Extended Price field is because it's from our GL which must match exactly (I can't be off by even one penny).

Please help me wrap my brain around this problem. I'll be posting on other Forums also because I know traffic is light on the weekends and I'm desperate! If my post is unclear, I will be right here to answer questions. Please help!!

LaRetta

[LaRetta]

Maybe because I'm so tired, but all I can think of is to go line by line and stopping if TransCode = Free Product, Sample, etc. Having globals to capture the Invoice#, free product price, then Go To Previous and test if its the same Invoice#, if ProductCode IsEmpty and if so, go forward again. If not, see if UnitPrice divided by gUnitPrice is a whole number. If so, divide UnitPrice by gUnitPrice, minus the Qty, minus Extended Price, go forward and add that Qty (which I would again have to capture in a global) to the FREE product and simply zero its price.

Yikes. With 279,000 lineitems, this back and forth through every record will take HOURS!!! There must be a quicker way! And the proper calculations for the math are refusing to enter my brain at all - testing whether plus or minus and adjusting accordingly.

[Ugo DI LUCA]

Hi LaRetta,

I'd go for the mix of relationship and calculations.

I'd assume the data comes sorted as in your example and that you rely on a serial to stay simple. If the later is not true, then you'd adjust the solution for sure.

Create 6 fields, 4 calculations and 2 globals

1. c_Previous = (Serial-1)&"-"&InvoiceID

2. c_Current = Serial&"-"&InvoiceID

--> Establish a relationship SjOnPrevious with c_Current matching c_Previous.

3. c_notFree = Quantity>0

4. gQuantity

5. c_matchFreeAndTwin (text result)= Case(not Mod(ExtendedPrice/;):SjOnPrevious:UnitPrice), serial&"

[Fenton]

They make you do this on the weekend?!

Here is a little file that attempts to do it. The main trick is to lookup the "previous" LineItemsID. If you're sure that no records were ever deleted, then you don't need this. But otherwise the previous ID may or may not the serial ID - 1. I did it for a text serial, but you'd have to adjust if you have a text prefix. It sets the correct previous ID, accounting for deletions.

This allows you to reference the earlier "linked" record that has the name, and also set quantities there, via a relationship, while running forward in the Loop.

You don't have to do it this way. You could also Loop forward, go back 1 when you hit a "freebie", set fields, then go forward 1, set fields, then Loop forward again.

Either way is a little confusing; I figured it was better to look up the previous ID once and use relationships. I could do it the other way also, but since you're on a deadline, you probably want to get started.

ProductsSerials.zip

[Ugo DI LUCA]

Oh wait...

c_matchFreeAndTwin (text result)= Case(not Mod(ExtendedPrice/;):SjOnPrevious:UnitPrice), serial&"

[Ugo DI LUCA]

Fenton,

It seems we're almost alone at this hour, while SuperBowl is on its way.

I'm just wondering what LaRetta thought about the ? in the Quantity field. I had assume it had to be calculated to determine how many were free, so the "?".

Now, I like the simple way you provided.

[LaRetta]

I'm here so sorry. Fell asleep and the ... on never mind. I'll read your suggestions. Thank you so much for helping me.

[LaRetta]

ProductSerials says it's severely damaged (I know the feeling ) I checked and it has an fp5 extensions! Ugo, did you download it fine? This data is fresh import with auto-enter calculated IDs. No records have been deleted.

Ugo, yours would work, I'm sure but my mind won't go there right now. Fenton's looks easier at this point. I'll try both though because, yes, I'm working on the weekend. I always work. Thanks so much for helping me, guys. I'll get to work on it. But I'd love to see that file.

L

[Ugo DI LUCA]

Yes it downloaded fine. Just tested it again. Would you like a copy of it by mail to see if the zip corrupted it ?

Looking back to his script though, it's not that easy too.

[LaRetta]

Thanks Ugo. Scripts can always be imported. I'll try againto download it. Thanks!

[LaRetta]

But otherwise the previous ID may or may not the serial ID - 1. I did it for a text serial, but you'd have to adjust if you have a text prefix. It sets the correct previous ID, accounting for deletions.

I don't understand this. And I don't understand the ?. My LineItem IDs are unique as "INV1000001, INV1000002 etc. Is that what you mean? So would I need to use the self-join? Sorry I'm a bit slow right now. And the ? is in a number field! I don't understand - did it get there frm the script or calculation? I thought ? would only appear in date fields. I think, otherthan those two questions, I can move forward. Thanks.

And i don't think I need to sort in InvID because unsorted they are in exact sequence, right.?

[Fenton]

You could just find the "freebies", set the "previous serial," as in my file, and only Loop through them. The relationship doesn't require the previous record to be in the Found set, either to read from or to write to. This would be much faster. (Also, always be in Form View when running Loops.) I just tested and it works.

ALERT! I made a mistake. The Relookup should NOT be on the SerialID, it should be on the cSerialPrev! It works either way, but using the existing LineItemsID (serialID) as the Relookup key might relookup for other fields, which you don't want and would slow it down. It has to be a lookup, to "use next lower value".

The text serial ID MUST be a uniform length, preferably with a prefix, or it won't sort, or work properly using "next lower." It might be better to create a LineItemsID calculation field as TextToNum(LineItemsID), result number. That would fix any text sorting anamolies.

ProductsSerials.zip

[LaRetta]

Hi Fenton, thanks so much. "The Relookup should NOT be on the SerialID"

You mean the calc lookup or the script? Or both?

[Ugo DI LUCA]

Fenton,

I made a Tour of your file.

I was to point the relookup trouble, as well as the fact that the c_previous should associate the InvoiceID, just in case 2 lines with the same product would follow within the line items.

Well, LaRetta is in a hurry, so she'd probably rely on this sample as it is clear. Nevertheless, I think by isolating first the freebies and their twins, the loop (according to the number of records she quoted) would be shorter.

[LaRetta]

Instead of wasting any more of your time with drivel, I think I'm going to lay down for an hour and see if I get any of my mind back. Otherwise, I simply can't pull this off. Thanks foir helping and I'll be in touch soon (I hope). 3 days of no sleep seems to be my limt.

Blessings!

L

[Fenton]

>My LineItem IDs are unique as "INV1000001, INV1000002 etc

Yes, that's what I meant. Otherwise "if not match, copy next lower" may miss. It's essential that it find the next lower serial in case some were deleted. If all yours are in the above range with the same prefix, it will work. Otherwise using calculation number fields is better (I added number calculation fields to my file, but they're not being used).

> ? in a number field.

That's what I saw in your original post, "change its Qty from zero to ?" I didn't see why, but that's what I saw; could be web display thing. Yeah, I set it in the script. It could be set to anything.

I didn't do anything with the extended price yet, 'cause my line total is a calculation field, but it would be the same routine as Qty.

I added a check to see if the total of the "freebie" divided evenly into its "parent" record. It's a related Mod calculation; you surely don't want to run this on all the records, just the "freebies." What happens with the "doesn't divide equally" ones?

Isolating the "freebies" is one reason for using the "previous serial" relationship method. Otherwise a Loop forward, step back, step forward, resume Loop may be easier to understand; but it would have to run on ALL records; slow on 279,000.

I could help write that also. I just started with the relational idea. Here's my file again, with a new "Fast" method, isolating only the freebies (and fixing the Relookup).

ProductsSerials.zip

[Ugo DI LUCA]

Just to explain it better,

If a product has no quantity, according to your text file, then it is a "freebie".

So instead of marking this record, I've chosen to return a multiline key (2 serials Ids in this case) if this was the case, using the calculation.

c_matchFreeAndTwin (text result)= Case(not Quantity, serial&"

[Fenton]

> as well as the fact that the c_previous should associate the InvoiceID, just in case 2 lines with the same product would follow within the line items.

> Well, LaRetta is in a hurry, so she'd probably rely on this sample as it is clear. Nevertheless, I think by isolating first the freebies and their twins, the loop (according to the number of records she quoted) would be shorter.

Yes, I don't know whether she really needs to check the InvoiceID. If a "freebie" always occurs after its parent, it would never overlap 2 invoices. If a freebie does not occur immediately after its parent, it's an error; no way to know what it is. I didn't do much error trapping. It is assumed the "parent-freebie" value pairs are consistent. The Mod() = Int (Mod()) may catch any that are out of order; but not necessarily.

If you use the "previous serial" relationship then you don't need the twins in the found set; you can get and set the twin's data via the relationship.

Take a look at my latest file, if you're still there, and see if you can see any flaws or improvements; 3 heads are better than 1. It's kind of pain to reset the data over and over; I guess it would be easier to just duplicate the file first (duh).

[Ugo DI LUCA]

It's surely late here Fenton.

I had trouble figuring out how it could work as a single lookup from the previous, and I'm still far from understanding what would make it work.

Could you present the data as it appears in LaRetta text docment, which is NO calculation and "00" appearing in either the price field and the quantity field in the freebies.

As I say, it is late. I'm not understanding how the lookup can update from the previous record without screwing the whole set.

[Fenton]

I'm not quite understanding where you're questioning. The data is either gotten from or set to the "previous" record via a relationship on the "actual previous serial ID." This previous ID is what is set via a lookup; because of deletions leaving gaps in the auto-enter serial.

Actually, now that I think of it, the lookup may not be needed. If you can be sure that every "freebie" has a parent directly before it, always with an ID that is 1 less than the number value of its ID, then the Relookup is not needed. It is only to be sure of getting the previous ID in case of deletion.

But deletion would never happen BETWEEN a parent and its freebie. LaRetta says that every freebie is directly after its parent in creation order. That's how you tell what it is, by looking at the order; there's no other way to tell. Other than the Mod()=Int(Mod()), which could probably tell, but not always (products that are different but cost the same).

So, a simple "LineItemsID - 1" should be sufficient.

If you have such a "serial - 1", then a relationship from it to the regular serial is going to give you the previous record. The previous record doesn't need to be in the found set, 'cause the relationship doesn't care about found sets.

As far as what you set in the Qty and other fields, and how they appear, can be done however you want.

[Ugo DI LUCA]

Here's where I was Fenton...and LaRetta.

And forget it, I understood with a beer...

PreviousMatch.fp5.zip

[LaRetta]

Oh, I'm embarrased. While you two have been working on this, I'm afraid I was sawing logs. I said an hour and I see it's 2 1/2!! I made my mom promise to throw cold water on me if I wouldn't wake up and she did! Very sorry about leaving you with my problem! Well, I think I'm a BIT clearer.

"If you can be sure that every "freebie" has a parent directly before it, always with an ID that is 1 less than the number value of its ID, then the Relookup is not needed. It is only to be sure of getting the previous ID in case of deletion."

Every freebie has a parent right before it and the IDs never have changed, are in exact text numerical sequence INV1000001 etc, and no records have ever been deleted (fresh import). However, not every line with a 0 value is a freebie - only the ones I indicated. Some lines are freight charges, payments, etc. However, on occasion a freebie was added at the bottom because the poor secretary would forget to add it immediately after the product and Peach doesn't allow insertion. I figure there will be a few that will need to be hand-altered. Also, since the secretary had to manually type the freebee price in, she would occasionally put the wrong price so it won't match perfectly. Again, that's why I wanted to be sure it matched the previous item's $ amount if divided, to be sure it belonged to that product before making the adjustment.

And one more thing I realised. If the original product had two listed but two were free, it would leave a product in the file with 0 quantity and 0 price once the freebie was pulled from it. So I'll also need to delete those out - although I should be able to FIND on that criteria (and also Type 'Stock' & TransCode 'Sale' and delete them after the fact.

I can't thank you two enough! I'll get to work with what you've given me. Upon a re-read, it actually makes sense what you're doing. I think I'm back in the brain-living again! Oh Ugo! You already have a beer? I was going to say I owe you two a keg!!!

Coffee time for me, though! I hope some day I can repay the favor, but I'll have to get much better at this business before I could help you two out of a design jam!

LaRetta

[LaRetta]

Oh, my apologies, Fenton! The '?' was attempting to indicate that the freebie quantity would change from 0 to (1 or 2 or whatever), depending upon how it divided into the product above it. God, this is a nightmare! Well, this whole conversion has been like this! They also changed how it calculates the extended total. That's why I need to leave it hardcoded and not just a calculation, because a calculation would be 'consistent' and the figures would change - they rounded up for awhile, then down, then trunced it.

But they're learning what database management is all about now that I'm here.

LaRetta

[Fenton]

I was taking a break also. I'll be fading in and out of conciousness, as my caffeine levels diminish -|

> Every freebie has a parent right before it and the IDs never have changed, are in exact text numerical sequence INV1000001 etc, and no records have ever been deleted (fresh import). However, not every line with a 0 value is a freebie - only the ones I indicated. Some lines are freight charges, payments, etc.

I was doing a Find on the text to locate the freebies, so other things could just not be found.

> However, on occasion a freebie was added at the bottom because the poor secretary would forget to add it immediately after the product and Peach doesn't allow insertion. I figure there will be a few that will need to be hand-altered.

This makes for a sticky situation. Because how would you tell if the freebie belonged to the record before it, or to an earlier entry? There is now a check to see if the freebie cost divides evenly into the product cost. (Actually this should be "unit cost", and you don't have a quantity for the freebie! I didn't even notice that. I adjusted my calculation, to figure out the quantity. I believe you mentioned something of this in your first post; but I tend to ignore things I don't like. Like the extended price :-)

This would leave out most "orphan" freebies; it would rarely accept one where the unit costs matched, but it was not actually the product (but how could you tell?); is that really a terrible thing? The money would still come out the same; just a minor descrepency in product count. I just don't see how you'd catch these, even manually.

> And one more thing I realised. If the original product had two listed but two were free, it would leave a product in the file with 0 quantity and 0 price. So I'll also need to delete those out - although I should be able to FIND on that criteria (and also type Stock & TransCode Sale and delete them after the fact.

The "freebie loop" as I wrote it has only the freebies in the found set. If you only wanted to keep 1 of the 2 records, you could set all the data into the first (which is not in the found set), then delete the 2nd (which is). Adding a deletion into a loop requires adjustment, so you continue to advance 1 w/out skipping (also there's sometimes problems on the last record, if it matches the criteria for deletion). I haven't done this.

ProductsSerials.zip

[LaRetta]

Spot on Fenton! And I'm rolling on it now!

"This would leave out most "orphan" freebies; it would rarely accept one where the unit costs matched, but it was not actually the product (but how could you tell?); is that really a terrible thing? The money would still come out the same; just a minor descrepency in product count. I just don't see how you'd catch these, even manually."

Yep, I know. BUT, a search for Type 'freebie' with 0 Qty would show them - any that didn't adjust could be Go To Related that Invoice# to view and manually adjusted in the invoice portal where it's clear which it belongs to. I'm going to require the Sales Rep fix their own. Same with any with pricing errors. And we would know because it would not be divisible equally. There is the possibility as you pointed out that a different product has the same value but, I don't care. One or two products off won't matter. I'm attempting to 1) bring the old data into synch with the new system and structure and 2) provide Management with critical data they've never been able to know before! That's the final punch in my presentation.

I only want to adjust those that match, if the product immediately above matches ... that's all we can do! I'm right with you on this one now, thanks! Allow youself to fade, my friend!

I agree a found set will be much easier to work with. I ran a simple looping Set Field through this file once before, jumping back then forward. It calculated out that it would take 10 hours! That's why I needed a relationship or some other way to handle it. It's clear and I'm awake! Thank God for Latte and you two!

LaRetta

[Fenton]

Yep, I'm fading. Using the relationship, if I understand the data correctly, you should be able to work with a found set of only the freebies.

I think I see what you mean by "in the invoice portal where it's clear which it belongs to." This is for those that did not divide evenly into their parent? Otherwise you'd never know they didn't belong to their immediate parent. And there's never two of these freebies right after one another, with neither having a product name? I hope not; not at this hour anyway -|

In any case, for those that did not match, at the end, it would not be terribly difficult to loop just within their own Invoice, using the division test until you found a match (first match, who cares :-?. Not 100% accurate, but about as good as you can do with that kind of data (no ID, no name, no quantity, only a total, in the wrong position).

You would have to Find and re-find those not matched, taking the first, then isolating its invoice. That should not be too difficult either, once you automate it. That's the trouble with 279,000 records, everything has to be automated, and work correctly the first time (hopefully). Good luck, and I hope to hear it was successful, in the morning :-)

[Fenton]

I was just going to sleep, but something was bothering me. A little voice said, "If you sort the Invoice descending by serial ID, while trying to match up the 'orphan freebies,' you will more likely hit the correct one first, on the logic that even if they forgot to enter it at the right time, it's more likely to be the one nearest the bottom." This is how I solve many problems, by letting my "database voice" annoy me whenever if feels something is not quite right. Hopefully it's satisfied now.

[Ugo DI LUCA]

Hi U2,

When I first saw LaRetta's post, I considered the option where not only one line could have been added. But then it didn't make sense and I chosed, as Fenton, to rely on the previous id/serial.

I tried to stay far from a loop on the whole file with that CopyAllRecords solution, even if a loop comparing Extended Price and catch those Mod results should have been efficient too, if data was entered into the lines with a logical way.

May be we've both been influenced by the title of the post too.

Fenton said:In any case, for those that did not match, at the end, it would not be terribly difficult to loop just within their own Invoice, using the division test until you found a match (first match, who cares -?. Not 100% accurate, but about as good as you can do with that kind of data (no ID, no name, no quantity, only a total, in the wrong position).

Not quite true Fenton, and as this stage, LaRetta's sample file throw all our theories down.

Look at the following data, extracted from the text document.

STEVIA POWDER 4 OZ ----CustomerReturn----- -12----5,39----- -64,68

STEVIA POWDER 100/B ----CustomerReturn----- -13----5,39----- -70,07

Misc. Debit Samples ---------Sample----------------- 00---- 00 ----- 5,39

There are 2 items with same Unit price in this Invoice. If the match from previous isn't the rule, to which product does the Free samples relate ?

Reading back the whole thread now, is it the point you were both referring as "Who cares" ?

[LaRetta]

Hi Ugo! Yep I'm still up. I have a successful migration!

Reading back the whole thread now, is it the point you were both referring as "Who cares"?

Care? Not I ... In theory, the sample returned would apply to the product directly above it. Data entry is told to enter the sale product then immediately the freebie. However, if forgotten, it would be stuck on the bottom because Peach can't insert and they didn't want to delete 10 items just to add one dumb sample. I don't blame them.

I took over this design and stepped into Office Manager position to quickly learn their admin structure. That's the first thing I decided to change. What a pain in the fanny! Not only that, if there are 15 free samples, we have to use a calculator to come up with the total to back out! Primitive!

And yes, I neglected to say that occasionally, there were more than one type of freebie after each product. Don't you hate it when a postee omits critcal information? But I flagged them before I ran this and Sales Reps can fix them. I have the reports that Management has never known - how much free product are given away and WHAT products.

Let's see: Right now they hand-write their orders (name, address, credit card info, the works), then enter everything again into a flat-file in ACT, hand them to a secretary who enters them in Peach. Errors? It's what this business lives by. But now, the data is in line with the new system and tomorrow they'll see what it's like to enter them directly into FM and it's done! Order IS the invoice. And they'll be able to find any customer with one search - instead of searching through 25 ACT databases to find someone while Customer holds on the phone. Do you think they'll complain?

Cheers! And thanks again for all the help. Of course, I'll have to repeat it for Feb 1 forward but we plan to network within the month and be up and running. And I have everything mapped and scripted (to migrate this last silly month)! That'll be a piece of cake.

Life is good! After this Orientation meeting (uh, in 3 hours actually), I think I'll sleep for a week. But I'm on a high now! Nothing beats the rush and high of success!

LaRetta

[Ugo DI LUCA]

Well,

Glad it was successfull then...

[Fenton]

Good morning. Glad to see it worked, at least as well as it could. You're right Ugo that there could be more than 1 sale per Invoice that matched the unit cost of the "freebie" (which we're pretty much estimating anyway, since we don't have a quantity). My last post, about looping descending, would help.

At some point I guess LaRetta was deciding that a margin of error was acceptable, as long as the money came out right; so the data could be entered into their accounting software, which would not accept the way they'd done it.