Discussions

Expand all | Collapse all

Need assistance with formula please

  • 1.  Need assistance with formula please

    Posted 08-16-2021 16:56
    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
    ------------------------------


  • 2.  RE: Need assistance with formula please

    Posted 08-16-2021 18:40
    Can you explain a bit about what you are trying to accomplish?

    Are those representing two different formula fields?


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



  • 3.  RE: Need assistance with formula please

    Posted 08-16-2021 19:33
    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.








  • 4.  RE: Need assistance with formula please

    Posted 08-17-2021 10:43
    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
    ------------------------------



  • 5.  RE: Need assistance with formula please

    Posted 08-17-2021 10:46
    …I want it to occur in one field only. Thx

    ------------------------------
    Lynne8817 Lynne8817
    ------------------------------



  • 6.  RE: Need assistance with formula please

    Posted 08-17-2021 10:44
    I'm not sure that this is for me?

    ------------------------------
    Lynne8817 Lynne8817
    ------------------------------



  • 7.  RE: Need assistance with formula please

    Posted 08-17-2021 11:27
    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?


  • 8.  RE: Need assistance with formula please

    Posted 08-17-2021 11:38
    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
    ------------------------------



  • 9.  RE: Need assistance with formula please

    Posted 08-17-2021 11:43
    Working with one field only - [PR PTUP BUDGET]

    In words,
    If the [Start-Hang date] is ON/BEFORE 7/26/21 and [Paint Garage] is true, I want ([Board Count]*[DW PT/UP (1) SLP]) to return that number to this field [PR PTUP BUDGET].
    If [Paint Garage] is false, I want ([BD CT PT/UP*[DW PT/UP (1) SLP]) to return that number to this  same field [PR PTUP BUDGET]

    IfIf the [Start-Hang date] is AFTER 7/26/21 and [Paint Garage] is , I want ([BOARD COUNT]*[DW PT/UP (1) SLP])+([BOARD COUNT]*[DW PT/UP (2) SLP]) to return that number to this field [PR PTUP BUDGET].
    If [Paint Garage] is false, I want ([BD CT PT/UP]*[DW PT/UP (1) SLP])+([BD CT PT/UP]*[DW PT/UP (2) SLP]) to return that number to this  same field [PR PTUP BUDGET]

    ------------------------------
    BuildPro
    ------------------------------



  • 10.  RE: Need assistance with formula please

    Posted 08-17-2021 12:00
    It can be either one of those...depending on whether [Paint Garage] checkbox is true or not.

    This is the original formula that worked.  
    If
    ([Paint Garage]=true,
    [BOARD COUNT]*[DW PT/UP (1) SLP],
    [BD CT PT/UP]*[DW PT/UP (1) SLP])

    I just need to change it if the start-hang is after 7/26/21 to reflect the addition of [BOARD COUNT]*[DW PT/UP (2) SLP] or ([BD CT PT/UP]*[DW PT/UP (2) SLP]


    ------------------------------
    BuildPro
    ------------------------------



  • 11.  RE: Need assistance with formula please

    Posted 08-17-2021 12:05
    Ahhh ok. This can be done with nested Ifs, or something like this:


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

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