Rank: Newbie
Joined: 7/16/2021
Posts: 1
|
All,
I'm processing an export that has two targets.
1. An export file
2. Updating the source table and flagging the record as sent.
The major complication is that information is spread across several records and all the records have to be present in order for the data to be exported. The method of doing this (this is pre-existing and cannot be changed) was to add a "NumberRecords" field, which contains the # of records that complete the information. Once that # of records is present, the data can be exported. The only way I could figure out how to perform that check was to use the Group by function. That works, so I'm good there.
Flagging those records as Processed is where I'm being tripped up. The records are coming to me in groups, and I can't figure out how to separate the groups back into records, or at least process the individual ID field values, to update the database. I only want to update the DB if the records are exported, so I have to be able to use the results of the Equals function in the mapping. Since the ID isn't exported, I can't chain the export, at least not as far as I can tell.
I have an example hopefully attached. The Input file contains records that mirror the above example:
ID,UserNumber,NumRecords,Value,Processed
1,A1234,2,AAAA,N
2,B2345,1,BBBB,N
3,C3456,1,CCCC,N
4,D4567,1,DDDD,N
5,A1234,2,AAAA,N
In that file, records 1 and 5 is the example of data being spread across two records, 2-4 are self-contained. If records 1 or 5 are deleted, notice in the Target Output file how the other record is not exported. If both are present, the data appears. Only UserNumber and Value are exported, so I'm able to use the groupings just fine there.
In the "Fake SQL" target, I'm simulating the commands being generated by a target DB component. That's a little misleading because the flexibility shown by generating the command (for example, using "IN" instead of "=" for the where clause) doesn't exist. No command appears for records 1 and 5 if either are deleted from the Input file, which is correct. The problem is that target's output is missing the command to update record 1 when all 5 records are in the Input file.
I get the following warning in the GUI's messages area, so the problem is apparent - I just can't figure how to work around it.
The previous value 'Update Blah Set Processed = 'Y' Where ID = 1' of field 'Command' was overwritten with 'Update Blah Set Processed = 'Y' Where ID = 5'.
The end result of this export should be
1. 4 records in the "Grouped Output" file
2. 5 update statements in the Fake SQL Output
3. if Record 1 or 5 is deleted from the Input file, the two targets should have 3 records each.
Any help would be greatly appreciated. If someone has a better way of doing this, that'd work as well.
PaulFile Attachment(s):
 HD Ticket Input.txt (1kb) downloaded 106 time(s).
HD Ticket.mfd (8kb) downloaded 114 time(s).
PaulG attached the following image(s):
|
Rank: Advanced Member
Joined: 2/27/2009
Posts: 565
|
Based on the data you supplied, I see no reason you can't use the UserNumber field in conjunction with chaining the text file component via a filter to the Fake SQL.
Output:
Quote:Update Blah Set Processed = 'Y' Where ID = 1
Update Blah Set Processed = 'Y' Where ID = 2
Update Blah Set Processed = 'Y' Where ID = 3
Update Blah Set Processed = 'Y' Where ID = 4
Update Blah Set Processed = 'Y' Where ID = 5
File Attachment(s):
HD Ticket.2021-08-03.mfd (9kb) downloaded 107 time(s).
K101 attached the following image(s):
|
