I have a file that contains a list of all products. However, whenever there's a price change to products the same list gets generated but with new updated prices that overrides the older price. So the effective start date lets me know if the prices have changed. I want to create a workflow that can only pull data where 'effective start date' matches today's date to populate that product's information into the final output.
I have tried using formulas shown below but Price shows as '0' and effective date as 'null'
Please advise
Sample Data:
Price List Name Product Number Currency Price Start Date End Date
(string)
US Commercial Products 210-595-2222 USD $1000 6/21/2017 12/31/2017
US Commercial Products 333-595-2221 USD $500 1/1/2017 12/31/2017
US Commercial Products 210-595-2221 USD $250 6/21/2017 12/31/2017
US Commercial Products 333-595-2223 USD $1150 11/1/2016 12/31/2017
The final file must be in the following format that contains the following fields and following order:
Mfg Name (leave blank) Product ID Product IDQualifier (Leave blank) Price Effective Date
Sample Output based on above data:
Mfg Name (leave blank) Product ID Product IDQualifier (Leave blank) Price Effective Date
210-595-2222 $1000 06/21/2017
210-595-2221 $250 06/21/2017
I'm using the following formulas to populate the price and the effective date fields in formula tool:
ProductID: IF [Start Date]=DateTimeNow() Then [Product Value] Else "" Endif
ProductPrice: IF [Start Date]=DateTimeNow() Then [Price] Else "" Endif
EffectiveDate: IF [Start Date]=DateTimeNow() Then [Start Date] Else "" EndIf
Gelöst! Gehe zu Lösung.
First thing I would check is your field types. You'll want to make sure your Start & End date fields are in standard DateTime format. Use the DateTime tool or the DateTimeParse formula to convert those two fields to DateTime type prior to the formulas you have listed below... if that doesn't fix it (or if you need assistance with the date formulas), let us know? Would be helpful at that point if you could attach a copy of your workflow with some dummy data so that we can check for other setup/configuration issues that might be causing issues. :)
Hope that helps!
Cheers,
NJ
Hi @arnorian
The reason the formula doesn't work is because you used DateTimeNow() instead of DateTimeToday(). DateTimeNow() will return today's date along with the time it is generated. The times won't match.
Here is how I created the output you wanted. First, I used a Filter Tool to find those that are effective today, then a Formula Tool to create the blank fields.
Let me know if you have any questions or additional problems.
And like @NicoleJohnson said, make sure that the start date and end date are Date data types.