I have 2 tables... Load Board Carriers The ...

[Brady_Lovell]

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?

[Mary_Jane_Pende]

There are a number of solutions here.

1. do you want to prevent users from selecting the carrier if the insurance has expired, or 2) do you want to just advise the shipper at the time they select a carrier with an expired insurance, OR 3) Do you want to send an email alert that the shipper selected a carrier with expired insurance.

Which one are your looking to do?

[Brady_Lovell]

@Mary_Jane_Pender 2 for sure, and 3 would be solid bonus!

[Mary_Jane_Pende]

No again, I am wrong, it should be Text.

Let me know if taking out the IF fixed things.

[Brady_Lovell]

@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?

[Mary_Jane_Pende]

What happens if you leave in the IF?

[Brady_Lovell]

@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!!!

[Mary_Jane_Pende]

sORRY I screwed up earlier.

Think my brain is baked for today.

Good night Brady.

[Brady_Lovell]

@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.

[Brady_Lovell]

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”,))

[Mary_Jane_Pende]

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

[Brady_Lovell]

@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?

[Mary_Jane_Pende]

OK Brady, there is a data type mismatch.

Make sure the CarrierInsExpy

column is a Date type column

[Brady_Lovell]

@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!

[Mary_Jane_Pende]

You are welcome Brady.

I always peruse the appsheet users community each night and I always learn something new.

[Brady_Lovell]

@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”,"") '.

[Mary_Jane_Pende]

Oooops.

Take out the IF

• I think that should do it.

[Mary_Jane_Pende]

Also, column type is wrong – should be Date not Text