Forum Discussion

DeepaPrashant1's avatar
DeepaPrashant1
Qrew Cadet
11 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
------------------------------
  • DeepaPrashant1's avatar
    DeepaPrashant1
    Qrew Cadet
    11 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
      11 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
        11 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
        ------------------------------