Discussions

Expand all | Collapse all

Date Formula as a Reference

  • 1.  Date Formula as a Reference

    Posted 09-06-2019 12:47
    Hello!

    I am trying to automatically relate records in a child table to a parent table based on the month that they are marked as "Complete".

    I have the date that they are marked complete in a field.

    My parent table has a record for each month, with a month field being marked as the first day for that month.

    I am trying to create an automatic relationship between these two tables. The relationship that is created for me is the child table referencing the month date in the parent table.
    I have found that it is possible to relate children to a parent using a formula. I have done it using a text formula reference field to relate the child to the parent automatically.

    The issue I am running in to is that if I change the Date (reference) field, to a Date (formula) (reference) field, the formula won't let me return a date, and requires me to return a text. I feel like this is a bug, and it is rather annoying.

    Is there any other way to do this? I would prefer to avoid automations, knowing that I was able to accomplish essentially the same task using just text. The main blocker here is the date formula field requiring me to return text, which isn't the proper type and makes the relationship fail.

    Thanks for any help!

    ------------------------------
    Sterling Long!
    ------------------------------


  • 2.  RE: Date Formula as a Reference

    Posted 09-06-2019 13:51
    That does not seem possible.  You should certainly be change your reference field from being a date field to a formula date field.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 3.  RE: Date Formula as a Reference

    Posted 09-06-2019 17:51

    I had a similar problem with a formula that returns a number.  For me it was a false positive error. When I ran a report on that table the formula appeared to calculate correctly. I continued to test and it works fine.  I put in a help desk request, but it closed as the formula worked.



    ------------------------------
    Richard Chagnon
    Northwestern University
    Chicago IL
    ------------------------------



  • 4.  RE: Date Formula as a Reference

    Posted 09-07-2019 10:14
    You are quite right. It looks like I just needed to save it and it would work. I suppose I was a under the assumption that I couldn't save it, because generally a formula field will stop you from saving if you aren't returning a value of the correct type.

    So now everything works beautifully.

    QB Really should fix this though. It's a bit confusing.

    Thanks!

    ------------------------------
    Sterling Long
    ------------------------------



  • 5.  RE: Date Formula as a Reference

    Posted 09-07-2019 10:18
    It appears as though that I can, QB was just telling me that it needed to be a text return value, not a date. Making it return a date gave me an error, but I could still save and the formula functioned properly.

    All is well. 

    Thank you!

    ------------------------------
    Sterling Long
    ------------------------------



  • 6.  RE: Date Formula as a Reference

    Posted 09-07-2019 13:49
    Well if you want to look at this in a more positive light, up until about two years ago there was no live editing in the formula box. You would save the formula and if it had an error it would tell you.
    A couple of years ago they put in the live error checking but it's not 100% perfect. So sometimes if you can't figure out the area just try saving and see if it takes.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 7.  RE: Date Formula as a Reference

    Posted 09-10-2019 06:44





  • 8.  RE: Date Formula as a Reference

    Posted 20 days ago
    I am trying to do something very similar to this - can you please share how you set up your formula?

    ------------------------------
    Nick Mesec
    ------------------------------



  • 9.  RE: Date Formula as a Reference

    Posted 20 days ago
    If you hope to elicit an answer on this forum you should start a new complete question.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------