Combine 2 records into one!

[LaRetta]

Hi everyone!

I have a General Ledger listing (data import from DOS-based AccPac), which has one GL# on one line and it

[djgogi]

If I've understood the question than you really don't have to use some looping script,

Instead create an field in source (or make an copy of your source file and than create an field) and run replace on field with formula Mod(Status(CurrentRecordNumber),2)

Records with 0 in field will be debits and those with 1 will be credits.

Now perform find on 1 (BTW the number of records in Found set should match the number of omitted records)

Perform Import Records from your destination file populating right fields (those for credits) with option ADD NEW RECORDS

Now back to your Source file and perform find on 0

Again perform Import Records from your destination file populating right fields (those for debits) but this time check option Replace data in current found set.

That's it.

Dj

[CobaltSky]

Hello LaRetta,

As Dj has pointed out, there might be several other ways to achieve what you want which might be easier or more efficient (or both) than a looping script.

However, as a matter of interest, the reason it is getting stuck on the first record is because you have the Go to Next Record/Request/Page [ ] command tucked inside an If[ ]...EndIf clause, as follows:

+ If ["IsEmpty(DebitAmount)"]

+[color:"white"]....Set Field [ DebitAmount, CreditAmount ]

+[color:"white"]....Go to Record/Request/Page [Next, Exit after last]

+ Endif

Which means that the script can only go to the next record if the DebitAmount field is empty. So, if there is a DebitAmount value on the first record, the script would have to stay there.

To add to the problem, the above If[ ]...EndIf clause is itself inside another If[ ]...EndIf which wraps around the whole contents of the loop.

If I understand you correctly, all the records have either a debit or a credit amount. That being the case, with the conditionals set up as they are at present, the script will always be unable to leave the first record it encounters which as a debit value.

If you move the Go to Record/Request/Page [Next, Exit after last] step so that it occurs on the line which immediately precedes the End Loop step, it will then be able to make progress. However you would also need to look at the placement of the other Endif commands, in order to achieve the desired effect.

[LaRetta]

Hi DJ

I'm sorry but I don't really understand what you're suggesting. Sometimes the *odd* or first record will be a debit, sometimes a credit. Will Mod(Status(CurrentRecordNumber),2) take that into account? Also you said, "the number of records in Found set should match the number of omitted records, but I don't see where (or when) I should omit! And if I import, how will FM know to apply the correct imported record to the existing records if I don't have a match field? Also, I will end up with the total number of records I started with, right? Should I then search and delete those without a GL# in both Debit & Credit?

LaRetta

[LaRetta]

Hi Ray

Oh, so that's what the indented EndIf is for

Well, I wanted to *get* this so I modified my script, still using the loop. I still don't understand how I could run this through a set of records without a loop? I will try DJ's idea also because I want to understand it all. Now my script goes through all records correctly except it skips setting the Credit# on those it set a debit and vice versa. The amounts filled in correctly. Script looks like:

CombineGL

Loop

If [ IsEmpty(DebitAmount) ]

Set Field [ gTmpC.num, GL# ]

Set Field [ gTmpD.num, "" ]

Else

Set Field [ gTmpD.num, GL# ]

Set Field [ gTmpC.num, "" ]

End If

Delete Record/Request

[ No dialog ]

If [ IsEmpty(DebitAmount) ]

Set Field [ Debit#, gTmpD.num ]

Else

Set Field [ Credit#, gTmpC.num ]

End If

If [ IsEmpty(DebitAmount) ]

Set Field [ DebitAmount, CreditAmount ]

Else

Set Field [ CreditAmount, DebitAmount ]

End If

Go to Record/Request/Page

[ Next, Exit after last ]

End Loop

I started with 5056 records and ended with 2528 so at least that part worked! Am I getting closer to understanding it?

LaRetta

[djgogi]

Ok

So I've made some assumption that obviously were wrong.

Those are:

1) The sequence of records is credit-debit-credit-debit ect.

2) I have also assumed that there is even number of records in your DB and that every debit record has it's corresponding Credit record.

The part 1 is easy to fix:

Instead of Mod function use replace step on the flag field (created before) as Case(Credit$,1)

Now, regarding part 2, first part looks OK

You said:

I started with 5056 records and ended with 2528.

Now the real problem is

Are those records (2528 copies already coupled, I mean looking at them in groups of 2 are they ALWAYS credit/debit association (no matter if sequence is credit-debit, or debit-credit)

In other words are they correlated?

If this is not the case than I really don't see an easy (or hard) way to programatically reassemble copies of credit-debit without an common identifier..

But if it's true than the same approach I've described could be applied.

So after running replace on the flag field, you'll end up with credit records having 1 and debit records having 0 in it.

To stress the point again. It is important that starting from first record every 2 records are One credit and One debit no matter the sequence and that they are correlated.

If all of above is true than the procedure I've described should work.

Dj

[LaRetta]

Hi DJ

Thank you for helping me. This dB (when unsorted) is in exact order - records 1 & 2 go together (although they may be credit/debit or debit/credit. So I understand (I think) your suggestion of using a calc (?) via the $ field to determine whether they are debit or credit entries. Yes, there is an equal number of records - two for each *transaction*.

Okay, I can use a calc to determine whether they are debit/credit. I created a calc (num, unstored) as: Case(IsEmpty(DebitAmount), 1, 0), so I can identify them. But the records currently exist in the dB; they are already imported I ended up with 2528 records after I ran my script (which was correct). My problem is that my script isn't exactly correct (see response to CobaltSky). I really want to try what you suggest but I'm just not sure, once I find the *credit* records, what to do from there.

So, I find the credits and omit them? Sorry I'm so thick on this DJ

LaRetta

[Razumovsky]

I think I see whats going on.

your records start with 3 non global fields that could contain GL# info: GL#(filled in in every record with data from import), Debit# and Credit# (both blank to be filled in by script). With this in mind, lets look at the script.

CombineGL

Loop

If [ IsEmpty(DebitAmount) ]

Set Field [ gTmpC.num, GL# ]

Set Field [ gTmpD.num, "" ]

Else

Set Field [ gTmpD.num, GL# ]

Set Field [ gTmpC.num, "" ]

End If

Delete Record/Request

[ No dialog ]

This much looks ok- you have now trapped the GL#, for the record you have just deleted in one of two globals depending on whether it is Debit or Credit. Continuing:

If [ IsEmpty(DebitAmount) ]

Set Field [ Debit#, gTmpD.num ]

Else

Set Field [ Credit#, gTmpC.num ]

End If

Here is the problem, Of the 2 fields Credit# and Debit#, you have just set one of them to the previous records GL# based on the data in your relevant global. However, nothing is telling the remaining Debit# or Credit# field to trap the data from the current records GL#. Try using replacing the rest of the script with the one below (I took the liberty of combining the remaining step under the same if/else statement for a simpler design)

If [ IsEmpty(DebitAmount) ]

Set Field [ Debit#, gTmpD.num ]

Set Field [ Credit#, GL# ]

Set Field [ DebitAmount, CreditAmount ]

Else

Set Field [ Credit#, gTmpC.num ]

Set Field [ Debit#, GL#]

Set Field [ CreditAmount, DebitAmount ]

End If

Go to Record/Request/Page

[ Next, Exit after last ]

End Loop

Keep in mind that this script will only work if the credit and debit entries imported are an even number and in diresct alternating snd sequential order in the found set, otherwise there will be big problems.

Also, as you said you were new to scripting, two useful hints: as a matter of principle, it is good to start all loop scripts with "go to record/request (first)" to make sure that the script does not start in the middle of the found set (unless thats what you want). A helpful tool in troubleshooting these unruly beasts is to liberally toss in some pause/resume steps so that you can verify visually that what you meant to happen actually did happen after each step/group of steps. (just remember to remove them when you're done!)

HTH

[LaRetta]

Oh thank you!

Yes, emmm, when I pasted my script the second time, I missed the first line with GoTo Record/Request [first]. Your script is much simpler; I really like the way you combined them. And it makes sense why I was missing part of it. Oh, I'm going to love scripts one of these days!!

Yep! That worked perfectly, thank you! BTW, I'm interested (no, VERY interested) in how I could have accomplished this easier so if CobaltSky or anyone else has a suggestion, I'd love to hear it. But I'm glad I had to learn this script anyway

LaRetta

[CobaltSky]

If you're looking for alternatives, here's another suggestion. Whether it's easier or not, I leave you to judge

1. Create an additional field called TransactionKey (a number field).

2. Unsort the records, then perform a Replace Contents procedure to set the TransactionKey field, with the formula:

Round(Status(CurrentRecordNumber) / 2, 0)

3. Create a stored calculating (number) field called cCredit.Key with the formula:

Case(IsEmpty(DebitAmount), TransactionKey, 0)

4. Create a self-join Relationship called MatchingCredit which matches the TransactionKey field to the cCredit.Key field.

5. Perform a find for all zeros in the cCredit.Key field

6. Run a Replace Contents on the Debit# field, using the formula:

MatchingCredit::Credit#

7. Find Omitted and Delete all.

Once the above structure is in place, you could create a script which contains steps 2, 5, 6 and 7, so that the process could be repeated with fresh data whenever required (if this is a consideration).

Or, If you'd like to try a variation on Dj's earlier suggestions, an alternative approach would be to perform steps 1 and 2 above, then:

3. Search with an = sign in the DebitAmount field (to find blank debits, and thus locate all the credit lines).

4. Save a copy of the file (with another name).

5. Delete the found set.

6. Import the copy of the file, but choose the 'Update matching records in current found set' option. Before oerforming the import, however, match up the TransactionKey fields in both files (if they aren't already) and click on the symbol between them to get a double header arrow (ie to match on that field) and then make sure that the on;y values that are going to be imported are the CreditAmount and the Credit# fields. Then set the import in motion.

7. Once the import is complete and you've confirned that the records have been correctly merged, delete the copy of the file.

Either of the above methods are workable. I'm not sure that either is markedly better than the other - or better than the script approach you started out with.

Perhaps the answer as to which is best depends on factors such as whether you'll have to do this at regular intervals, or whether this in a once off. If you have to repeat the exercise with similar data, then a script has the advantage that once it is set up and tested, it will be there to use again whenever you need it. Perhaps the first of the two approaches I've suggested here would lend itself a little better to scripting, but only insofar as step 7 would require the use of an external plug-in such as Troi File (unless you're happy to simply leave the copy lying around.

I daresay there are other approaches and variations on a theme. FileMaker is nothing if not rich with possibilities.

[LaRetta]

Hi Ray

Hopefully, this is a one-time issue. I have applied all of the suggestions and they all work beautifully!! I sure learned a lot today. Thanks again everyone!!

LaRetta