Method Community

Again, I'm retrieving the duration of activities from the Activities table for each customer recordID and populating them into a column I created in the Customer table called "PDFHours".

I'm using SQL to do an advanced query tied to a button click, and that seems to work mostly, but I'm having problems perhaps dealing with some of the junk in the activity table.

Due to file attachments (I think), when I export the Activity table into a CSV file, there are a number of rows where the aActivityCompanyName column (the first column in the table) is populated with an extended character sequence of gibberish (like Ilgb3RzurMGLIQ7v1CtIhTLL59RbXvRvy4aWLVZTS7rfEXCa5Mr but much longer) for some of the rows. Many times these rows are one after the other, and then there are normal records after for a while. None of these rows have a RecordID. This type of text also shows up in a custom column I created called "Certification1Doc", but those ones have rRecord IDs.

I believe this is the source of the Action Sequence Order:40 error I get when trying to pull the results from the ActualDuration field and populate the PDFHours field for each customer.

How do I remove these records from the table? Is it possible? Could something else be impacting this?

This didn't arise until I started compiling hours for activities between certain dates. In my SQL I have caveats like [RecordID] > 0, ISDATE (DueDateStart) = 1 and ISDATE (DueDateEnd) = 1, but if those are working, I have to believe that it's the garbage entries that are the problem.

Please let me know how/if I can remove them or deal with them.

Thanks

I forgot to mention: in the cases where the extended character sequences are in the first column (ActivityCompanyName), there are no other data in any of the other columns for those rows.

When it's in the Certification1Doc column, the row is an actual record. Could it be that the excessive text is overflowing the entire cell and spilling over into other areas?

Perhaps I should just delete the certification column altogether?

Hi MikeB,

It looks like you may have attached a docuement to some records.  That's normally where you see the text like your seeing.  You can delete the records by creating a simple grid with checkboxes, loop through the grid and delete those records that are checked.

My question though is how those records got a document attached to it to begin with?  It looks to me that there are some actions messed up and not updating the record correctly.  With Excel, it can sometimes look at though data is spilling over into other cells.  I'd suggest doing an export and excluding the fields that have the strange text.  Perhaps this can give you an idea of what the record is and maybe trace where the data came from.

- Adam

Yes, documents are attached to certain activities. Exporting them without those fields results in good data, but I think that it's messing up updating all activities when I retrieve values from that table.

Just to be clear, I'm looping through the Customer table and retrieving values from the Activity table.

I get the Action Sequence Order:40 Error after a while (not immediately).

Again, the error says Cannot update field in table "PDF Hours". So it is crashing when trying to update the PDFHours field in the Customer table by pulling data from a field in the Activity table.

Interestingly, I recently added date parameters so that I'm only grabbing a subset of all activities.

My SQL advanced script is:

[RecordID] IS NOT NULL AND AttachmentFileName IS NULL AND [Entity_RecordID]='customerfromrow' AND [ScheduledDuration] >= 0 AND [ActivityType]<>'Admin:Active Selling Status' AND ISDATE([DueDateEnd]) = 1 AND [DueDateEnd] BETWEEN 'CalcDateStart' AND 'CalcDateEnd'

MikeB,

Have you tried updating the field with a static value to see if the error is in how the data is getting pulled from the Activity table?  While the error happens at the update action, the source may be somewhere else.  Perhaps limit the records being updated and put in a field to insert the values from the Activity table.  This will cofirm the right values are being pulled.

- Adam

It works now, but I'm not sure why.

It tried it with single customers, and with accounts that had the same parent name, and those worked. I noticed that the "Where" option allowed me to say:

Where RecordID (in the customer table) = Value From Screen RecordID (in the Customer table). Strange how entering the same field on both ends is allowed.

I changed it to:

Where RecordID (in the customer table) = Value from Row Entity_RecordID (in the Activity table) and that works.

Thanks

MikeB,

Glad to hear its working.  What you changed it to makes much more sense, since you're then comparing the recordID to the current row in the loop.  

- Adam