Method Community

Hi All -

I have a custom field in Quickbooks called 'Import SKU' that I use for all items in the item list. I use this field to communicate unique SKU information to our warehouse. I have created a grid in Method that I use to export the daily invoice detail from the 'Invoice Line' table. My grid includes this 'Import SKU' field and pulls the correct SKU detail for every line item with the exception of the 'Subtotal' line. You cannot create a custom field in the Quickbooks item list for 'Subtotal', so this field is blank on my grid for all subtotal line items. I need for this blank field to read 'Subtotal' when I filter my grid to view the daily invoices. How would I go about doing this? I think that I need to create a button that pulls from the 'Item' column, where it actually says 'Subtotal', and insert into the 'Import SKU' field. Would I perform an action set to loop through my grid?

Thanks in advance for your help.

Birch

Hi Birch,

Is the Import SKU field a linked field in the InvoiceLine table? If not how is that data populated? Are you exporting the grid using the Export Grid to Excel action?

-Michael

Hi Michael -

No, I did not have to link it. The custom field was already listed as a field in the 'InvoiceLine' table (I do not have the option to delete it either). So, the data is automatically populated next to the quantity and amount for each item on the invoices.

Thanks,

Birch

Birch,

You can try an SQL Override. Something along the lines of:

 CASE WHEN [ImportSKU]=' ' Or [ImportSKU] IS NULL THEN 'Subtotal' ELSE [ ImportSku] END

 **Note the above code may not won't but its would be the format used, fields names are in [ ], regualr text is in ' '

This may work in your situation, I would recommend watching the webinars on SQL overrides HERE for more info.

-Michael

Hi Michael -

Sorry for the delayed response. After watching the webinar, I have been trying to think through the potential problems of using the SQL override that you suggested above. In the event that the field 'ImportSKU' is blank for an item that is not the subtotal line (ie. if someone accidentally leaves the importSKU field blank on an invoice in QB for 'item A'), I do not want to take the chance of having 'Subtotal' automatically generated to all blank fields. Is there a way for me to loop through my grid and use a set of actions that would enter the word 'Subtotal' into the 'ImportSKU' field IF the 'Item' field from that row = 'Subtotal'. (Possibly a character function?)

Let me know if that makes sense. Thanks for all of your help!

Birch

Birch,

You can apply the same SQL override concept to only be applied to items of type subtotal. You could also loop through your items and have a condition that checks to see if the item type is subtotal and if it is update that row to have subtotal in the ImportSku field. You may need to link ItemType from the item table into the InvoiceLine table for this to work.

-Michael

I added a linked field 'ItemType' to the 'InvoiceLine' table. Then, I tried to add the following 'on screen load' actions to the advanced screen properties and I received an error message that 'An error has occured in the Update Field In Table action. Please verify all data being entered is correct.' 

1. Start loop through table 'InvoiceLIne' where 'ItemType' is equal to type in 'Subtotal'

2. Update field 'ImportSKU' in table 'InvoiceLine' with 'value from row' 'Item' where the field 'item' equals type in 'subtotal'

3. End loop through table

4. Refresh grid

Do you know what could be causing this error?

Thanks, Birch

Birch,

It's probably is a better idea to put these action in the same location where you generate the report that displays this info and not on the OnScreenLoad for the screen. Are you updating based on RecordID from Row? If not that could be the issue. Also another suggestion is to loop through grid instead of through the table should make the process faster.

-Michael