Forum Discussion

BuildPro's avatar
BuildPro
Qrew Captain
4 years ago

Need assistance with formula please

I know there are several thing wrong with this, but I wanted to make sure you knew what I was trying to accomplish.  thank you

If
([Start - Hang]<Date(2021,7,26) and [Paint Garage]=true,
[BOARD COUNT]*[DW PT/UP (1) SLP],
[BD CT PT/UP]*[DW PT/UP (1) SLP],

If
([Start - Hang]>Date(2021,7,26) and [Paint Garage]=true,
([BOARD COUNT]*[DW PT/UP (1) SLP])+([BOARD COUNT]*[DW PT/UP (2) SLP]),
([BD CT PT/UP]*[DW PT/UP (1) SLP])+([BD CT PT/UP]*[DW PT/UP (2) SLP]))

------------------------------
Lynne8817 Lynne8817
------------------------------
Formulas and functions
  • MichaelTamoush's avatar
    MichaelTamoush
    Qrew Captain
    4 years ago
    Can you explain a bit about what you are trying to accomplish?

    Are those representing two different formula fields?


    ------------------------------
    Mike Tamoush
    ------------------------------
    • MarjorieMcKenna's avatar
      MarjorieMcKenna
      Qrew Trainee
      4 years ago
      I actually just figured it out. There are 2 tables and I needed a lookup field from one of them. I went into the table to table relationships and was able to add it there. So far I think I did it correctly. Thanks

      --
      Kind Regards,
      Marjorie McKenna
      Construction Program Specialist
      ADOA – General Services Division | State of Arizona
      1400 W Washington St, Suite B-200
      P: 602-542-1983 | marjorie.mckenna@azdoa.gov


      How am I doing?  Please take a moment to answer a few questions.





      • BuildPro's avatar
        BuildPro
        Qrew Captain
        4 years ago
        I'm not sure that this is for me?

        ------------------------------
        Lynne8817 Lynne8817
        ------------------------------
    • BuildPro's avatar
      BuildPro
      Qrew Captain
      4 years ago
      Basically, if the Start - hang date is before 7/26/2021, I want one set the formula (without the + stuff to happen)… 
      if the start-hang date is after 7/26/2021, I want the formula with the + stuff to happen…

      ------------------------------
      Lynne8817 Lynne8817
      ------------------------------
      • AustinK's avatar
        AustinK
        Qrew Commander
        4 years ago
        The problem is your formula appears to not be functional at all even if it was set up in that way. For example the top if statement would not actually do anything. 


        1. ([Start - Hang]<Date(2021,7,26) and [Paint Garage]=true, 
        2. [BOARD COUNT]*[DW PT/UP (1) SLP],
        3. [BD CT PT/UP]*[DW PT/UP (1) SLP],

        If we break it down by line..

        1. If the start date is before 7/26/21 and paint garage is true, this works. Then it continues to the next line as the criteria has been matched based on the date and checkbox.
        2. Here it multiplies board count by some other field, but this is where it begins to break. Assuming this was a text or numeric field it would show whatever calculation was there. However you have a comma at the end and another line with no way for the formula to arrive there.
        3. Then we get to this line of the formula which is impossible to reach without the right condition above it.

        Can you describe exactly what this is trying to do? You have more issues than just trying to get the dates to work here. In my example above how would you want line 2 and 3 to behave? What data are you wanting to end up with?
    • BuildPro's avatar
      BuildPro
      Qrew Captain
      4 years ago
      …I want it to occur in one field only. Thx

      ------------------------------
      Lynne8817 Lynne8817
      ------------------------------
      • MichaelTamoush's avatar
        MichaelTamoush
        Qrew Captain
        4 years ago
        Sorry, I didn't notice one was a greater than and one was a less than sign. I thought both conditions were identical.

        As Michael Karr said, there is a confusion somewhere with what result you are trying to produce. If we isolate the first part of the formula (as Michael did) and the 'Start-Hang is Less Than the desired date', which number do you want to have shown in this field?

        [BOARD COUNT]*[DW PT/UP (1) SLP]

        or

        [BD CT PT/UP]*[DW PT/UP (1) SLP]

        ------------------------------
        Michael Tamoush
        ------------------------------