I have 2 tablesโฆ
Load Board Carriers
The Load Board is my main table with all of my orders. Carriers is a database of trucking companies that I hire to haul produce for me. Each record in the Carriers database has a cargo insurance policy expiration date.
In my Load Board table, I have column [Carrier] that has a Valid_If =Carriers[Name], which populates a DDL of all the names of the trucking companies listed in the Carriers table.
When I choose a trucking company from the DDL on the Load Board, I want a workflow to be fired when the insurance expiration date (which is on the Carriers table) of that particular trucking company, is pass the current day.
Is this possible?
There are a number of solutions here.
Which one are your looking to do?
@Mary_Jane_Pender 2 for sure, and 3 would be solid bonus!
No again, I am wrong, it should be Text.
Let me know if taking out the IF fixed things.
@Mary_Jane_Pender I donโt think that did it?
=([CarrierInsExpy] <= TODAY(), โEXPIREDโ,"")
DOnโt you have to have to IF to get the word โExpiredโ to show up in the [Insurance Check] column?
What happens if you leave in the IF?
@Mary_Jane_Pender I moved the expression from the Show_If to the App Formula. Working like a charm! This is absolutely incredibly I can not thank you enough!!!
sORRY I screwed up earlier.
Think my brain is baked for today.
Good night Brady.
@Mary_Jane_Pender Would you happen to know if itโs possible to also include the column [Liability End] into the formulaโฆ
= Any(SELECT(Carriers[Cargo End], [Name]=[_THISROW].[CARRIER]))
Iโm sure I can create another column on my Load Board table and repeat the same process, but didnโt know if there was a way to reword the formula above instead. If either the Carrierโs Liability or Cargo is expired, they canโt haul the load.
Nevermind, I think all I have to do is create another virtual column withโฆ
= Any(SELECT(Carriers[Liability End], [Name]=[_THISROW].[CARRIER]))
then I might be able to do something likeโฆ
=IF([CarrierInsExpy] <= TODAY(), โEXPIREDโ, IF([LiabilityInsExpy] <= TODAY(), โEXPIREDโ,))
OK.
So โฆ for 2) add a Virtual column on your Load Board table that picks up the Insurance Expiry date of the Carrier - call it CarrierInsExpy โ put this formula in the App Formula field = Any(SELECT(Carrier[Insurance Expiry Date], [Carrier]=[_THISROW].[CARRIER].
Then add a column right after the Carrier name on the Load Board table and call this column InsuranceCheck.
In the Show-If column constraint put in a formula like this =IF([CarrierInsExpy <= TODAY(), โEXPIRED INSURANCEโ,"")
This field will โshowโ
itself immediately to the shipper.
They can choose another carrier or cancel the shipment until the carrier Insurance Expiry date is greater than today. For number 3) Create a work flow to generate an email to the person who should be alerted, formula = ([InsuranceCheck] <> โโ)
When someone goes to save the Load Board record an email will kick off if the InsuranceCheck field is not blank.
Hope this helped.
mj
@Mary_Jane_Pender Iโm getting a decimal as a result of the app formula for the virtual column CarrierInsExpyโฆ
= Any(SELECT(Carriers[Cargo End], [Name]=[_THISROW].[CARRIER]))
โThe expression is valid but its result type โDecimalโ is not compatible with the expected type โDateโโ
Any ideas why I may be getting a decimal instead of a date?
OK Brady, there is a data type mismatch.
Make sure the CarrierInsExpy
column is a Date type column
@Mary_Jane_Pender The [Cargo End] column was set as a โDecimalโ column. I overlooked it. You have been a tremendous help! This has been one of our biggest inefficiencies. I canโt thank you enough!
You are welcome Brady.
I always peruse the appsheet users community each night and I always learn something new.
@Mary_Jane_Pender Almost there, false alarm lol. DO you know why Iโm getting this error in the Show_If of the [Insurance Check] columnโฆ
Column Type โTextโ has an invalid show_if constraint '=IF([CarrierInsExpy] <= TODAY(), โEXPIREDโ,"") '.
Oooops.
Take out the IF
Also, column type is wrong โ should be Date not Text
User | Count |
---|---|
44 | |
34 | |
27 | |
23 | |
15 |