Multi calculation in an IFTTT ?

[Solix]

Hello,

English is not my native language, so I apologize if there are any mistakes.

After a record, I would like to add a value in another form based on a formula. This formula is in three parts.

First part:

• scoreUser = sum of the score fields of form 1 filtered on the current user and on the field subject=math
• scoreTotal = sum of the score fields of form 1 filtered on the field subject=math
• value1 = scoreUser/scoreTotal

Second part:

• nbUser = count of the fields of form 2 filtered on the user and on the field theme=education
• nbTotal = count of the fields of form 2 filtered on the field theme=education
• value2 = nbUser/nbTotal

Third part:

• if value1 >=0.5 and value2>=0.5 then value3 = yes, otherwise no

Then, if value3=yes then I want to update a third form with the user's name

My first idea is to do the calculations in dedicated fields in form 1 but I don't find that right. I would prefer that the calculation is done after registration, so via IFTTT but I don't see how to do it. Do you have any ideas? It seems too complicated for an IFTTT, right ?

[Alex Medved _ConfiForms_ · Accepted solution]

Hi

Not understanding everything, but may be for the calculations/aggregations you can use the queryAndAggregate and updating the values you want with IFTTTs

This actually might be used in the formula field you may consider to create - then you dont really need any IFTTTs for the first and second parts, or may be event the 3rd

Alex

[Solix]

Hi,

I tried using queryAndAggregate but I couldn't get it to work. I also tried queryAndSet because there are a few more examples, but without success. Do you have any examples of using this virtual function?

Do I use it in rules for field definition with set Expression?

[Alex Medved _ConfiForms_]

I can take a look at the setup you have and check the way you are using the queryAndAggregate... May be I can spot the issue

If you share a page storage format it would help me to have the same configuration as you and work from that

[Solix]

I can't share a page storage format but, i'm going to try explaining my structure.

formA as :

- a text field : score

formB as :

- a user field : user

- a formula field : totalUserScore

=> expression : (id.queryAndAggregate(formA:123456;createdBy:[entry.user];score))

I have an error : could not be calculated, unknown operator "." at position 3

[Alex Medved _ConfiForms_]

Hi

There are 2 possibilities and you seems to be mixing them together: formula and field expression

Using in formula

[entry.id.queryAndAggregate(formA:@self;createdBy:[entry.user];score)]

Using directly in a field, without a need of an extra field definition

id.queryAndAggregate(formA:@self;createdBy:[entry.user];score)

Complete storage format

<ac:structured-macro ac:name="confiform" ac:schema-version="1" ac:macro-id="28c654ff-fffb-4cf0-9aa5-437ffc689328"><ac:parameter ac:name="formName">formA</ac:parameter><ac:rich-text-body><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="33df37d0-abdf-4431-bb28-33a5e5994def"><ac:parameter ac:name="fieldName">score</ac:parameter><ac:parameter ac:name="fieldLabel">score</ac:parameter><ac:parameter ac:name="type">number</ac:parameter></ac:structured-macro></p><ac:structured-macro ac:name="confiform-entry-register" ac:schema-version="1" ac:macro-id="dd2e3447-3da0-428a-8959-beb463f08169"><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><br /></p></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="confiform" ac:schema-version="1" ac:macro-id="fa34c68e-4f8e-42ae-86fb-44af8af72deb"><ac:parameter ac:name="formName">formB</ac:parameter><ac:rich-text-body><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="91aa6a20-0c67-4218-a992-9fa3377698bb"><ac:parameter ac:name="fieldName">user</ac:parameter><ac:parameter ac:name="fieldLabel">user</ac:parameter><ac:parameter ac:name="type">user</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="0fac6baa-8441-41b4-8364-e1df131ab35a"><ac:parameter ac:name="fieldName">totalUserScore</ac:parameter><ac:parameter ac:name="fieldLabel">totalUserScore</ac:parameter><ac:parameter ac:name="extras">[entry.id.queryAndAggregate(formA:@self;createdBy:[entry.user];score)]</ac:parameter><ac:parameter ac:name="type">formula</ac:parameter></ac:structured-macro></p><ac:structured-macro ac:name="confiform-entry-register" ac:schema-version="1" ac:macro-id="ffb44fee-2fff-4cb3-807b-2c9d4ce0aed5"><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><br /></p></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="0177ed16-7b05-432f-9ae6-5c9f815dd686"><ac:parameter ac:name="formName">formB</ac:parameter><ac:rich-text-body><p><ac:structured-macro ac:name="confiform-field" ac:schema-version="1" ac:macro-id="deafc7b6-88a8-4ef5-9cbc-7839614e7a10"><ac:parameter ac:name="fieldName">user</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field" ac:schema-version="1" ac:macro-id="2b1264f6-8eb5-49c9-8169-8a38f1b5fa85"><ac:parameter ac:name="fieldName">totalUserScore</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field" ac:schema-version="1" ac:macro-id="12fb9480-bf2c-4c47-9821-ecb0195a6d20"><ac:parameter ac:name="overrideLabel">total score</ac:parameter><ac:parameter ac:name="fieldName">id.queryAndAggregate(formA:@self;createdBy:[entry.user];score)</ac:parameter></ac:structured-macro></p></ac:rich-text-body></ac:structured-macro><p><br /></p>

Alex

[Solix]

Thank you !!! It works!

I'm having a little trouble knowing when to use one or the other.