Hello,
Im trying to do payroll for my employee and need to calculate fields from other app by date and update field in current app.
Have date fields in both apps . What is best way to do that.
Thank you
@ivica sorry Iām not sure I fully understand your question, if you can try and explain it more I will definitely try and help you.
If you could expand on what you are trying to achieve and if there is a relationship already between the two apps.
Jasonā¦ Appreciate your helpā¦ Here is a bit more details.
I have 2 apps
First app is main with a bunch of fields ā¦ including two date fields for start date and end date (in my case pickup and delivery) and price field.
In the second app ā¦ Im trying to filter 1st app by date and calculate price fields - to get total and update 2nd app field - called Total. (sent screenshot on previous post).
I have two date fields on 2nd app also where I can select range that I like to filter 1st app by date
Hopefully that this can help understand a bit better.
1 Like
@ivica this is practically identical to what @SanjaNikolic was trying to do a few weeks back (you donāt work in the same place do you?).
Anyway at least to my knowledge you canāt search for records in a record calculation field you can only perform actions based on the record or related records (in either direction). If you have your main app records related to the collection app then a calculation to find the total is simple:
However, I am assuming that you donāt have that so it needs automation to search for the records that meet your criteria and then perform an action on them (either do the relevant math and add the total to the record or relate the delivery records so that the calculation field works).
Now how the automation works depends on a few other things that I donāt know, such as is the ācollectionsā record rolling monthly or is generated for one moment in time (say at the end of the month). If it is rolling then I would probably add relations to it as you went so that the calculation field updated say through the month if it is a point in time created once a month as an example then I would probably create an automation that ran every month and searched for the relevant āmain appā records created your āroll up recordā and added a total to a number field
If you donāt want an automation then add a relation field to your collections app and manually relate the main app record to the collections app manually and use a calculation field to total the related records.
I am not sure if any of the above makes sense to you if you want help with the automation then feel free to send me more details (you can DM).
One key thing is that the search is after or before not equals or after; therefore, you need the -1day and +1day in your search.
1 Like
@SanjaNikolic and I do work togetherā¦ right - good catchā¦ 
I guess we canāt calculate inside one app from data from another appā¦ We would need to make it thru automatization and appā¦
So - we will display and create PDF with that kind of reportā¦ How the SUM function will work on automatization? after sorting by date range. Looking at your 2nd photo.
Is there a way to update record on current with SUM from automatisation ?
You most definitely can calculate inside one app based on data from a different one - however, there must be a relation between the records involved.
The above is from something quite old but it works, it finds the records I am interested in then adds up all the durations, converts it to seconds and finally adds the numbers to the record.
Okay so I have tried to write an automation that may match your needs, I have also tried to keep the code to a minimum. This works on the principle that you have at least three apps:
The main app is related to the Person app and the name field must contain the title from the Person app you could make the title of the Main app a calculation field which uses the title from the person app within it something simple like:
`${ļ»æ@All of Nameļ»æ} - ${ļ»æ@Start Date (pickup)ļ»æ} to ${ļ»æ@End Date (Delivery)ļ»æ}`
Although you may want to throw some date formatting in there as well
Our Main app has 4 records for two different people spread across the week, the automation happens in the āCollections appā
- We find all the records in the main app that fall in our Date window (you can use the method used for your PDF).
- This is the code bit sorry I canāt think right now of an easy way around it, what we want is a deduped array of the people, I have done it in a calculation block:
jsonata(`$distinct(fields[field_id=505028].values.value.title[])`).evaluate(record_collection__2438002___main_app)
You need to replace the field ID with the id of your relation field:
you then need to replace
record_collection__2438002___main_app with the record_collection_ variable that will come up when you start typing that.2.5. Now we have an array that in my case contains two names in yours it could be more or less.
Important: you may notice that the code written above is slightly different from my screenshot - use my code above, the screenshot one is likely to fail if there is only one person we are going to loop through each person.
3. Clear your collection
4. Recreate your collection but this time limit to the person
5. Use a rollup block to sum the price fields from your Main app
6. Create a new record adding the relevant details
Then we end up with two records in the collections app one for each person with the total of there records from the Main app:
Hopefully the above makes sense and you can pull some bits from it to make yours work, I have done it quite quickly, sorry itās a bit messy in places.
Just going back to the calculation field as the individual records in the main app are now linked to a person we can add a calculation field to the person record and get a grand total:
This however is not date specific so wonāt help you in this instance but could be handy:
