Welcome, Guest
You have to register before you can post on our site.



Search Forums

(Advanced Search)

Forum Statistics
» Members: 378
» Latest member: to8kitd45ome824w
» Forum threads: 1,148
» Forum posts: 5,983

Full Statistics

Online Users
There are currently 19 online users.
» 0 Member(s) | 19 Guest(s)

Latest Threads
Question about documents
Forum: Administrator Support
Last Post: rslemer
3 hours ago
» Replies: 0
» Views: 3
Error on created a new us...
Forum: Administrator Support
Last Post: rslemer
4 hours ago
» Replies: 0
» Views: 4
[solved] How 2fa Activiat...
Forum: Administrator Support
Last Post: rslemer
Yesterday, 02:19 PM
» Replies: 4
» Views: 87
revised ṕt-lang translati...
Forum: Administrator Support
Last Post: joebordes
Yesterday, 02:19 PM
» Replies: 1
» Views: 29
My 2 cents from being a u...
Forum: coreBOS Development
Last Post: joebordes
Yesterday, 02:05 PM
» Replies: 11
» Views: 215
Doubt - default filter fo...
Forum: Administrator Support
Last Post: joebordes
10-19-2018, 10:23 PM
» Replies: 1
» Views: 41
Sharing access with a Gro...
Forum: Administrator Support
Last Post: joebordes
10-19-2018, 10:21 PM
» Replies: 3
» Views: 44
[solved] Error click in l...
Forum: Administrator Support
Last Post: joebordes
10-16-2018, 09:32 PM
» Replies: 5
» Views: 134
[solved] translation for ...
Forum: Administrator Support
Last Post: joebordes
10-16-2018, 08:56 PM
» Replies: 1
» Views: 46
[solved] translation for ...
Forum: Administrator Support
Last Post: joebordes
10-16-2018, 08:56 PM
» Replies: 1
» Views: 50

  How to sum invoices on a salesorder and record the 'fully invoiced' date
Posted by: Guido1982 - 09-23-2018, 12:18 PM - Forum: Administrator Support - No Replies

Say you have the following use case:

You create salesorders to keep track of you sales, but your company has the habit of not invoicing the salesorder in one invoice. You want to record the date that a SalesOrder is fully invoiced, meaning: when did I create the invoice that totally invoiced this SalesOrder?

Well, here is what you would do.

First off, update your coreBOS! We discovered a small bug that we fixed here. Without this change, this will not work.

Next, create some fields on the salesorder module:

  • One for the sum of all invoices
  • One to record the date we're looking for in the first place
Of course create the fields with the right types.

Next, you need to create a text field on invoices that will hold the SalesOrder no. that the Invoice was created from or related to. We need that later to match the invoices to the salesorder. Call it anything you like.

Now we create the workflow on the Invoice module. I created the workflow to check on every save, but you can adjust that if your needs are different, but do understand that it may not work exactly as described here if you do.

First you create a task that gets the salesorder no. on the invoice field you created. Use an 'update field' task type and don't let it evaluate conditions on execution: we want this to happen always. Add a field to fill (the one you just created on invoices) and set it to 'use field'. use
$(salesorder_id : (SalesOrder) salesorder_no)
(or just select the Salesorder no. field from the dropdown, will do the same thing).

We now have a task that gets the salesorder no. and fills that into our custom field on the invoice, every time the invoice is saved. Now let's create the second task that will use that field to update the sum of all invoices on the salesorder based on that Salesorder no.

Create a second task in the workflow that you call 'sum all invoices on salesorder field' (or something like that in your language). Select the 'update field' tasktype again and don't evaluate conditions. Again, we need this to happen always. Select the 'sum invoices' field you created in the beginning as the field to update. Now use this code to sum all the invoice amounts into the field (Beware, adjust the custom field name here):
aggregation('sum','Invoice','sum_nettotal','[cf_1248,e,$(salesorder_id : (SalesOrder) salesorder_no),or]')
You need to replace 'cf_1248' for your own custom field no. That will be the field no. that you save the SalesOrder no. on in your Invoices. So again, this is the custom field on Invoices that you filled one task earlier.

What this does is: it uses the 'sum' method of the 'aggregation' function to get all the 'sum_nettotal' values of the 'Invoice' records. In the second to last parameter we say: 'cf_1248' (should be your custom field name) needs to be equal (the 'e') to the 'salesorder_no' field of the related SalesOrder. The 'or' is mandatory to glue more conditions, even if we don't have any. So this is how we match the invoices to the salesorder or: get only the invoices that belong to the salesorder we are updating.

We now have a mechanism in place that updates a field on salesorders with the totally invoiced amount. Beware, I use the sum_nettotal field here. If you need some other total, replace all instances so far (maybe you want the value with taxes, or with grand discount deducted).

Now we want to record the date that the salesorder is totally invoiced. So we want to prevent updating the date field when the 'sum' field on salesorders is lower that the salesorder total, and we want to prevent updating the date field after is has been recorded once. Say we edit an old invoice to change some date field or mark it as Paid, we don't want to affect the date field on SalesOrders when we do that (at least in this example, your needs may vary).

So we create a third task on the workflow we've been working on. This will again be an 'update field' task type. This time, we need to check the 'evaluate conditions' box and create some conditions. The first condition will prevent us from altering the date field once it has been filled once. Create the condition and select the date field we created on salesorders, choose the 'is' condition and leave the field right to it empty. Essentially we are saying "only do this if the date field is empty". Now create a second condition and leave the 'glue' to 'and' (the default). Select the 'sum' field we filled in the second task and select the condition 'greater than or equal to' (or just 'equal to' if you only want to do this when it equals but want to prevent filling the date when you over-invoice). Now in the right-most field you can select the field you want to match against. In my case this resulted in
$(salesorder_id : (SalesOrder) sum_nettotal)
but again, your needs may be different. Anyway, this matches against the sum_nettotal field of the salesorder. Since that's also the field we used on Invoices I would advise you to use the same fields on the entire worflow, otherwise you would be comparing pears to apples.

Now add a field to update and select 'SalesOrder' from the first dropdown. Because this is a reference field, a new dropdown will appear with all the fields from salesorders. There you can select the date field you created in the beginning. Now just select 'formula' when you click the rightmost field and enter
in the box. This will get the current date for you.

So there you have it. Whenever invoices are created or edited, they will update the salesorder with the totally invoiced amount and record the date of the first 'totally invoice' occurance for you. Any questions or remarks: please respond below.

Print this item

  A regular expression to assert Workflow expressions
Posted by: Guido1982 - 09-21-2018, 07:14 PM - Forum: coreBOS Development - No Replies

Just for fun I wrote a regular expression that would assert and capture a vtlib expression in 'normal' and 'poorly' written formats. I also tested some deliberately wrong formatted ones. Tested them on https://www.regexpal.com/. The expression is:

PHP Code:
\$\((\w+) ?: ?\((\w+)\) ?(\w+)\) 

The 'right' variations I came up with are:

$(reffield : (ModuleName) field)
$(reffield: (ModuleName) field)
$(reffield :(ModuleName) field)
$(reffield:(ModuleName) field)
$(reffield : (ModuleName)field)
$(reffield: (ModuleName)field)
$(reffield :(ModuleName)field)

The 'wrong' ones I tested are:

$reffield: (ModuleName) field
$(reffield) : ModuleName (field)
$(retfield : ModuleName field)

Maybe we could use this one day, or just for fun keep coming up with correctly formatted or incorrectly formatted ones.

Print this item

  workflow with delay - modified date
Posted by: rslemer - 09-21-2018, 11:54 AM - Forum: Administrator Support - Replies (7)

I created a new workflow to send a email 7 days after last modified date

but system sent email always workflow is executed

I scheduled my workflow to shedule, on some days of week, only

What I did wrong?

Attached Files Thumbnail(s)
Print this item

  Date fields: current date is autofilled on editing a record
Posted by: Guido1982 - 09-18-2018, 10:39 AM - Forum: Administrator Support - Replies (6)

I noticed that a lot of the datefields fill in the current date when editing a record. I would like to block this behaviour al together, the application should never autofill date fields. Is there an easy way I can do this?

Print this item

  fetch_array vs FetchRow
Posted by: Guido1982 - 09-17-2018, 08:11 PM - Forum: coreBOS Development - No Replies

I noticed something in the August blog, a speed comparison between fetch_array and FetchRow. Joe, could you elaborate a little on this? What is the difference between the two, in speed, usage and results?

Print this item

  GenDoc questions
Posted by: addady2 - 09-16-2018, 01:00 PM - Forum: Administrator Support - Replies (6)


I have several questions regarding GenDoc extension.

I Find in this forum old post regarding GenDoc online demo. https://demo.coreboscrm.com , but it don't seems to have GenDoc
Is there other any online demo?

Will GenDoc work on the task / todo module?

Dose sending generated pdf as email attach to the customer from the CRM need payed subscription.

Thank you

Print this item

  [ solved ] Module campaign not allowed input values correctly
Posted by: rslemer - 09-14-2018, 08:37 PM - Forum: Administrator Support - Replies (3)

I changed my user settings for use a value like as 999.999,00 - point is group separator and comma is decimal point, but only in campaign module, system not allowed input values in this format

In other modules, works fine ...

Attached Files Thumbnail(s)
Print this item

  theme mltheme - error at create a new workflow in campaigns
Posted by: rslemer - 09-14-2018, 08:32 PM - Forum: Administrator Support - Replies (3)

In my installation, a strange error occurs, window to create a new workflow appears behind a main window

see attached image

only when use a theme mltheme ....

Attached Files Thumbnail(s)
Print this item

  Doubt - block user click on mass edit button
Posted by: rslemer - 09-14-2018, 11:34 AM - Forum: Administrator Support - Replies (1)

There any way I block user use the mass edition button?

Print this item

  Doubt - Send email wih workflow when smtp server is inactive
Posted by: rslemer - 09-13-2018, 01:17 PM - Forum: Administrator Support - Replies (1)

I was inactive smtp server and my workflow sends an email to certain events

How will react to the server as an old message?

Older messages will send when active smtp again?

If yes, is it possible to clean all older messages?

Print this item