Forum Discussion

DeepaPrashant1's avatar
DeepaPrashant1
Qrew Cadet
7 months ago

Variable drop down field

Hello,

I have an interesting request. My QB app is pulling in entries and I am capturing the posting date of the entry / record. I can convert the posting date to a fiscal period such as Oct'23 using a formula field. I need the user to now select the fiscal period in which the entry will be reversed. I initially just created a static drop down field but received a request to make it variable so it only shows (in the drop down list) future fiscal periods. 

So if posting date is 10-01-2023...Fiscal period (Formula)= "Oct'23"

Drop down list should show:

Oct'23

Nov'23

Dec'23

Jan'24 etc

If posting date is 05-29-2023 and Fiscal period (Formula) is "Jun'23"

Drop down list should show:

May'23

Jun'23

Jul'23

Aug'23

Sep'23

Oct'23

Nov'23

Dec'23

Jan'24 etc

What's the best way to achieve this?

Thanks.

Deepa



------------------------------
Deepa Prashant
------------------------------

4 Replies

Sort By
  • DeepaPrashant1's avatar
    DeepaPrashant1
    Qrew Cadet
    7 months ago

    Correcting and clarifying my question above.

    Using API's to create records in QB. 

    I have an interesting request. My QB app is pulling in entries and I am capturing the posting date of the entry / record. I can convert the posting date to a fiscal period such as Oct'23 using a formula field. I need the user to now select the fiscal period in which the entry will be reversed. I initially just created a static drop down field but received a request to make it variable so it only shows (in the drop down list) future fiscal periods. 

    So if posting date is 10-01-2023...Fiscal period (Formula)= "Oct'23"

    Drop down list should show:

    Oct'23

    Nov'23

    Dec'23

    Jan'24 etc

    If posting date is 05-29-2023 and Fiscal period (Formula) is "Jun'23" as May 29th is fiscal June in our company.

    Drop down list should show:

    Jun'23

    Jul'23

    Aug'23

    Sep'23

    Oct'23

    Nov'23

    Dec'23

    Jan'24 etc

    What's the best way to achieve this?

    Thanks.

    Deepa



    ------------------------------
    Deepa Prashant
    ------------------------------
    • MikeTamoush's avatar
      MikeTamoush
      Qrew Commander
      7 months ago

      I would First Create a table called Months and Fiscal Years. There would be 4 fields: Month, Year, Fiscal Year, Last Day of Month (date field)

      Preload this using an Excel Sheet import, for the next 10 or 20 years.

      Use a relationship where One Month and Fiscal Year has Many of your Current table.

      On the current table, Make a formula that simply extracts the fiscal year from your Posting Date=Fiscal Year in New Table. Then use a conditional dropdown to only show the pre-loaded entries where Fiscal Year from Posting Date. The report you would use would have a filter Last Day of month>=Today(). This would eliminate all the past months.



      ------------------------------
      Mike Tamoush
      ------------------------------

      • DeepaPrashant1's avatar
        DeepaPrashant1
        Qrew Cadet
        7 months ago

        Thank you Mike.

        The trouble is in getting the prior periods to not show. The Posting date need not be today. The analysts could be editing the record a week from the Posting date and I need the drop down to only show the current period onwards.

        So based on the Posting Date in the Child table is there a way to create a conditional drop down and pull all future periods as a drop down from the parent table?

        Thanks



        ------------------------------
        Deepa Prashant
        ------------------------------