I have some workflows to update fields in quotes and invoices that work fine for quotes and invoices that are created within the CRM. However, they don’t work for imported quotes and invoices. I feel like there’s a missing relationship or association in the database for imports that is automatically created when you create a quote in the CRM and convert it to an invoice.
Any help would be appreciated as I have many imported quotes and invoices.
Versions:
SuiteCRM 7.10.7
PHP 7.2
SQL Server 2014
Windows Server 2012 R2
Chrome 71.0.3578.98
Thank you for your response. Guess I’m a little confused as to how the workflow will function just because I’m doing a mass assign but won’t run by editing each individual imported record.
I have a workflow in place that runs when a field is modified on a quote and will update the corresponding field on the related invoice (which works for non-imported records). It sounds like you’re saying that this workflow will run on imports if I do a mass assign instead of editing the records? If so, what’s the difference?
It seems as though there is a table field value that dictates whether a quote or invoice is an import or not. Where is this tie-in in the database?
If a Workflow runs on a record modification, it should run likewise from a mass-assign, and vice-versa. They are both record modifications (one individual, one in batch).
I don’t see a reason for a workflow to detect whether a record comes from an import or not. I don’t think that’s what’s going on, at least not directly. There might be some indirect effect (a missing id somewhere?)
Can you please document exactly how your Workflow is constructed? I’d like to take a look at things in specific (knowing the module, all the settings, the exact conditions, etc.).
You can just write it out in text, but if you want to use screenshots you can put them here:
Ok, so to back track for a minute…are you saying that workflows should work on imported quotes and invoices?
I. Quotes/Invoices Logic
In the quotes (which I’ve renamed Estimates), I have a field called “Estimate Name” which is the pre-existing name field renamed.
I removed the option from the quotes/estimates action menu to convert to invoice. We are driving this functionality through another quote/estimate field (checkbox) that, when checked, another workflow runs and creates an invoice/job that is tied to the quote/estimate. All of this works great.
In the invoices (which I’ve renamed Jobs), I have a field called “Job Name” which is the pre-existing name field renamed.
When I change an estimate name, the associated job’s job name will update to match the estimate name and vice versa.
This workflow works on quotes/estimates that I create in the app and invoices/jobs (that are created using our workflow process explained above) but does not run on imported quotes/estimates or invoices/jobs.
workflows will not run at the time of the import, but…
after importing, the imported records should behave just the same as any other record regarding workflows. So, editing the record manually should trigger the “modified records” workflow.
My guess is that something about your import is making the records slightly different, and it is causing the workflow to fail. Try checking the database via phpMyAdmin.
Also check if you have a custom fields table called aos_quotes_cstm.
Check how your records look and compare imported records to normal records. Any id missing? Is the name stored where it should be? Are the relationships properly created?
Hi. I do have a custom fields table called aos_quotes_cstm. The number of quotes in this table matches aos_quotes. Thanks for the schema links. Does the aos_quotes_aos_invoices_c table contain quotes that were converted into invoices? I’ve checked my tables and haven’t noticed anything else out of place or missing. Thought that maybe aos_quotes_aos_invoices_c table might be the connection between these 2 modules.
Yes, the connection between the two tables is aos_quotes_aos_invoices_c
The import won’t do that automatically, probably that’s what’s missing.
If you need a more structured import you are better off writing a PHP importer script and use Beans. But it can also be done “brute-force” by SQL in the database.
You can test if one of your records starts working with the Workflow when you add entries in the relationship tables.
Thank you. Was able to manually add a record to the aos_quotes_aos_invoices_c table and now the workflow does run.
Separate Issue: This particular workflow is causing the system to hang. I did notice that if I uncheck repeated runs on 1 of the workflows, then the other workflow runs normally. The workflow won’t work properly if repeated runs is unchecked as a user can change a quote or invoice name as many times as they like. NOTE: These workflows did run fine before. Is it possible that workflows can become corrupt.
When you are in the Workflows module, there is an option on the left sidebar menu called “View process audit”.
When you have a crashed process there sometimes it blocks others. The “aow_processed” table in the database let’s you do a lower level analysis. BTW, that’s the table that tracks the items already processed for the “repeated runs” limitation.
About the hanging workflow, you probably have a message in one of your logs ith some clues.
I don’t think the Workflow became corrupt, but rather that something new about the workflow, or about the records it’s hanging, is creating problems, or triggering a bug. Remember you’ve been editing things in the database directly, something might not be entirely as expected…
My workflow uses a calculated formula to add to a custom field the value of another custom field when this is modified:
all_import_sources_c gets the value of import_source_c appended when the workflow detects that import_source_c has been modified.
The workflow works fine when records are modified within the CRM but does nothing when I import trying to update existing records.
Doing this manually is impossible and running on all records without checking if the field has changed may be too heavy I believe.
Any suggestions?
The following is the calculated field:
{ifThenElse({and({lessThan({position({P0};{P1})};0)};{greaterThan({length({P1})};0)})};{ifThenElse({greaterThan({length({P0})};0)};{P0} / {P1};{P1})};{P0})}
Where:
{P0} is all_import_sources_c
and
{P1} is import_source_c
There are fields from both tables accounts and accounts_cstm
The “trigger field” is a custom field (therefore in the _cstm table).
I have created a few versions of the same workflow:
. repeated runs ticked/unticked
. run on all records
. run on modified records
. with / without a condition that checks “any change” to the field that I need to check
All work very well if I modify a record from within SuiteCRM.
But it doesn’t run if I import a record that modifies the specific “trigger field”.
I also noticed another issue: I am unable to import the latitude/longitude fields (They are in the _cstm table ad they belong to the maps funcionality): they produce an import error (but this is a different story, but it is strange because I am importing back an export in which I have modified just a few fields, but not these). Similarly I get errors trying to import back “Non Primary E-mails”: for this I think there should be a specific format, which I will dig into later, however the column was there in the export!)
I don’t know, maybe you could try going through the import with a debugger hooked up to your instance? To see exactly where it decides to ignore the record.
If not, then maybe all that you can do is open an Issue on Github for this…
Good to hear you are able to import invoices! Are they from Quickbooks? Are all rows coming in like invoice numbers etc? Other posts seem to indicate this is a problem.