I am using a repeating group for Users (datatype/table), inside it I have a field that needs to show years of total job experience based on job start/end dates from user’s job history data in another datatype/table i.e. User_Experience.
So far I have been able to get the Start and End dates from the User_Experience table to show up in the repeating group, but I am having trouble calculating the difference between the two dates to get years. Can someone kindly guide me on this, please?
The table structure for 2 tables and data is something like following:
The following image shows (start/end dates) for 2 jobs for one user, so I need to calculate the duration of all those jobs for each user, and add them up in days/365 to get years.
Your textbox should be something like this: (Search for User Job Historys:first item's End Date - Search for User Job Historys:first item's Start Date) formatted as days / 365.
Simply, subtract the dates and calculate the years. I guess formatted as years doesn’t exist, that’s why I got formatted as days and divided by 365. Of course, if you want to be more accurate, you should take the leap years into account as well
Be aware that I used the first item after the search, not each item because I feel it will return one entry for each user. IF not, you should modify it according to your data.
Good Morning! Thanks a lot for your response, it gave me a good direction .
Now the thing is I need to calculate the Job_end_date Minus Job_start_date (then divide by 365) for each user in the repeating group; while obtaining each user’s name, and city from the User table and their respective job dates from the User_Experience table. Something like the following example:
For that I need to run the search/calculation for each item, but when i do that it does Not show me the minus sign option in the drop-down list of available options, as shown below. Can you kindly advise what I may be doing wrong?
Yes, that is where I am stuck on how to handle each item to do that minus calculation since there are multiple jobs for each user. I can show the results using the syntax below but not able to calculate the duration. Thanks a lot!
Following shows that since user Linda she has 2 jobs, so it shows first the end dates of both jobs, and then the start dates for both her jobs. Not sure how to make my logic to do that minus for each job and add up the days/duration for all jobs to get total #days for all jobs.
Probably the text should be something like this: Search for User Job Historys : format as text : split by(,) : each item : converted to number : sum
And in the format as text, content to show per each item will be (This User Job History's End Date - This User Job History's Start Date) formatted as days / 365, and delimiter will be ,.
Simply, what is happening is:
You calculate the year for each work experience (in the format as text content)
You combined them with comma
You separate them by comma
You convert them to numbers
You sum.
And yes, combining/separating seems like a redundant step but this is to mimick map operation in Bubble which doesn’t exist.
Hergin, sorry for the late response. I am still working on this. Its a little tricky for me. I will get back soon with my update. thanks a lot for your kind response.
Ok, I was finally able to create it following your instructions, and it worked perfectly! The issue I am still trying to figure out is if the end date is Empty (because the user is still working at that job), then I need to use Today’s date for this duration calculation., or else I see the duration negative as shown below. Is there any quick way to replace the empty end date with today’s date?
This trick did not work, as it started giving 10 years for everything. I am trying to troubleshoot. I noticed your formula for inner text conditions has brackets around date subtractions, but in my app, I am not able to add those brackets.
Yes you need to enable brackets from the settings. Sometimes we need them. I don’t know if that would affect in this case, but still. Enable them here:
Carefully matched and the only difference I could find between yours and mine, is the following highlighted RG definition. I am now trying to test a new repeating group and modify it from there.
My main use case requires showing a list of all users, their location, and years of experience, which is why I am using users as the type of content in my repeating group. looks like i need to focus there. thanks a lot!
Created brand new “Employee” table exactly as yours with exactly same data as yours, matched everything on a new test page to yours to the dot. and still getting different results. Not sure why is this happening. Will review with fresh mind tomorrow. thanks a lot! Good night!
.
