I have two tables. "Processing" and "Monthly Interest Rate".
I have a field in the processing table that is called [interest rate]
I have fields in the "interest rate table" called [JAN 2018], [FEB 2018], [MAR 2018] and so on.
I want to pull the value of the corespondent field from table "Monthly Interest Rate"
If we are in the month of February of 2018, than the rate that would apply is the value on the field [FEB 2018] and it would fill out the field [interest rate] that is in the table "processing".
I would appreciate if someone could help me make that formula?
Thank you!
I have a field in the processing table that is called [interest rate]
I have fields in the "interest rate table" called [JAN 2018], [FEB 2018], [MAR 2018] and so on.
I want to pull the value of the corespondent field from table "Monthly Interest Rate"
If we are in the month of February of 2018, than the rate that would apply is the value on the field [FEB 2018] and it would fill out the field [interest rate] that is in the table "processing".
I would appreciate if someone could help me make that formula?
Thank you!
 492 Points
Posted 1 year ago
QuickBaseCoach App Dev./Training, Champion
 59,838 Points
You can set the Key field of your interest rate table to be a date field of the first of the month. Then in your detail table make a formula field of
FirstDayOfMonth ([My date field])
Them use that to make a relationship to the interest rate table and the lookup the interest rate.
FirstDayOfMonth ([My date field])
Them use that to make a relationship to the interest rate table and the lookup the interest rate.
 492 Points
Hey Mark,
Thank you so much for your help. I dont think i was able to accomplish what you said.
I made a quick 1 minuto video to explain better what I am trying to do here. If you or anyone could help me figure the best way to do this, It would be very much appreciated.
Thank you so much for your help. I dont think i was able to accomplish what you said.
I made a quick 1 minuto video to explain better what I am trying to do here. If you or anyone could help me figure the best way to do this, It would be very much appreciated.
QuickBaseCoach App Dev./Training, Champion
 59,818 Points
The video would not play for me. I’m outside the US. Would that be why?
QuickBaseCoach App Dev./Training, Champion
 59,838 Points
Sergio
I read your question again.
The best way to do the tables is to have a separate record for each month in the interest rates table. Set the key field to be a date field type and enter the date for the first of the month in each record along with the interest rate.
I read your question again.
The best way to do the tables is to have a separate record for each month in the interest rates table. Set the key field to be a date field type and enter the date for the first of the month in each record along with the interest rate.
 492 Points
QuickBaseCoach App Dev./Training, Champion
 59,838 Points
 492 Points
Mark, not yet.
I think there is something missing.
Its probably easier for you to understand if you were able to see the video. I dont understand why you cant if I didnt restrict the region.
Anyways, I created the records, one for each month with the interest rate for each month.
In the other table where I have the transactions, each transactions has its value, and its interest rate according which month it is.
What I needed is if I have 100 transactions on Jan, the interest rate would be whatever the interest rate it is on the interest rate table.
Right now the way I do is, change the default value of that field in the begining of the month, so every record I import after that date, will have that default value on the rate.
Even the guys on the quickbase support team were not able to get that working for me.
I think there is something missing.
Its probably easier for you to understand if you were able to see the video. I dont understand why you cant if I didnt restrict the region.
Anyways, I created the records, one for each month with the interest rate for each month.
In the other table where I have the transactions, each transactions has its value, and its interest rate according which month it is.
What I needed is if I have 100 transactions on Jan, the interest rate would be whatever the interest rate it is on the interest rate table.
Right now the way I do is, change the default value of that field in the begining of the month, so every record I import after that date, will have that default value on the rate.
Even the guys on the quickbase support team were not able to get that working for me.
QuickBaseCoach App Dev./Training, Champion
 59,838 Points
Sergio
I did watch the video via the link you posted.
What you want to do is very simple, so it will work.
Please answer these questions.
1. Do you have a table of Interest rates, with 1 record per month.
2. Is the key field a date field?
3. Is that Key field loaded with the 1st of each month.
4. Do you have a field on the detailed transaction records with a formula of FirstDayOfMonth([Transaction date])
5. Is there a relationship based on that FirstDayOfMonth([Transaction date]) field?
6. Are you puling down the interest rate in that relationship?
I did watch the video via the link you posted.
What you want to do is very simple, so it will work.
Please answer these questions.
1. Do you have a table of Interest rates, with 1 record per month.
2. Is the key field a date field?
3. Is that Key field loaded with the 1st of each month.
4. Do you have a field on the detailed transaction records with a formula of FirstDayOfMonth([Transaction date])
5. Is there a relationship based on that FirstDayOfMonth([Transaction date]) field?
6. Are you puling down the interest rate in that relationship?
 492 Points
Hi Mark,
Yes i did.
I does work for records that have the data of transaction on the first day of the month, but its does not work for the other days.
Dont I need to add something to the formula to pick up the other days?
FirstDayOfMonth([DATE OF TRANSACTION])
It only works for transactions done on the first day, the other days it doesnt work,
Yes i did.
I does work for records that have the data of transaction on the first day of the month, but its does not work for the other days.
Dont I need to add something to the formula to pick up the other days?
FirstDayOfMonth([DATE OF TRANSACTION])
It only works for transactions done on the first day, the other days it doesnt work,
QuickBaseCoach App Dev./Training, Champion
 59,838 Points
No, that is correct. What field did you use the build the relationship? Maybe you used the transaction date to build the relationship and not the formula field for the first day of the month. You need to use that formula field.
 492 Points
Got it!!!
Works!
Thank you so much!
Works!
Thank you so much!
QuickBaseCoach App Dev./Training, Champion
 59,818 Points
So in the end, did you simply do what I originally posted?
You can set the Key field of your interest rate table to be a date field of the first of the month. Then in your detail table make a formula field of
FirstDayOfMonth ([My date field])
Them use that to make a relationship to the interest rate table and the lookup the interest rate.
You can set the Key field of your interest rate table to be a date field of the first of the month. Then in your detail table make a formula field of
FirstDayOfMonth ([My date field])
Them use that to make a relationship to the interest rate table and the lookup the interest rate.
 492 Points
I got one other formula that support was not able to do it. If you have a chance to take a look it would be a blessing!
https://community.quickbase.com/quickbase/topics/howtocreateaformulafieldthatwilladdalltra...
https://community.quickbase.com/quickbase/topics/howtocreateaformulafieldthatwilladdalltra...
QuickBaseCoach App Dev./Training, Champion
 59,818 Points
Sergio,
I think I would have to look at your actual app on a screen sharing call, but i would need to be on the clock, for that time. Contact me via the email address on my website f you would like to email directly with me. QuickBaseCoach.com
I think I would have to look at your actual app on a screen sharing call, but i would need to be on the clock, for that time. Contact me via the email address on my website f you would like to email directly with me. QuickBaseCoach.com
 492 Points
Hey Mark.
Thank you so much for all the help!
I figure what the problem was.
It works perfectly!
Really appreciate it.
Thank you so much for all the help!
I figure what the problem was.
It works perfectly!
Really appreciate it.
Related Categories

Ask about Quick Base functionality
 916 Conversations
 63 Followers