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
------------------------------
• Can you explain a bit about what you are trying to accomplish?

Are those representing two different formula fields?

------------------------------
Mike Tamoush
------------------------------
• 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.

• I'm not sure that this is for me?

------------------------------
Lynne8817 Lynne8817
------------------------------
• 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
Qrew Commander
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?
• …I want it to occur in one field only. Thx

------------------------------
Lynne8817 Lynne8817
------------------------------
• 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
------------------------------