Changing a status field based upon field content in a separate table

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • Answered
Hi:

I am trying to stop the issuance of an invoice our client if we are still awaiting an invoice from a sub contractor.  Basically..... if purchase value is null..... then change status field in a different table.

Just looking for some general pointers.  Im sure others here have already done similar modifications.

Any help provided is GREATLY! appreciated.

Regards,

Dirk
Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of Justin Torrence

Justin Torrence

  • 648 Points 500 badge 2x thumb
You could do this one of two ways, both involve using Automations.
1. Create a scheduled Automation to check the Purchase Value field daily. For any records where it is null, change the status on related record. Might need to use the Copy Record(s) action for this one. (A little bit more complex)
2. In the table where status is, set the default value of status to "Awaiting Purchase Value" (Or whatever your planned status is) and then set up an automation that triggers when Purchase Value field is changed, and the action for the automation should change the status to whatever you want after that happens.

Let me know if that makes sense
Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb
Hi Justin:

Thank you for suggestion.

Your help is greatly appreciated.

Regards,

Dirk
Photo of Jake Rattner

Jake Rattner

  • 660 Points 500 badge 2x thumb
Hi Dirk, 

What 'type' is the status field?

I'd start by bringing the 'invoice purchase value' into the table with the 'Status' field via a table-to-table connection.

If the 'Status' field is a formula field,   Then you can update the formula with an 'If-Statement' like: 
If([IsNull(Invoice Value),Old Status,New Status) 
For the 'Old Status' argument in the formula you can paste in whatever formula you were previously using in the 'Status' field

If your Status field is a multiple-choice field will depend on your workflow, but you could use a form rule to prevent users updating the status to whatever status allows an invoice to be sent if the purchase value is null.

Let me know if you have any questions.

Jake Rattner | Solutions Architect
(847) 927-1427 | jrattner@quandarycg.com
Quandary Knowledge Base
Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb
Jake:

I think your last suggestion is exactly what I am trying to do.

I will let you know if my efforts are successful.

Thank you,

Dirk
Photo of Jake Rattner

Jake Rattner

  • 660 Points 500 badge 2x thumb
Great!  Let me know how it goes.
Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb
Hi Jake:

Having trouble with the Form Rule.  It looks like I can hide a particular field (create Invoice), but I can't set up the conditions.

I am trying to Hide [Create Invoice] in Work Order Form, if [Price] in Sub Table Materials is equal to zero.  I can't seem to modify relationship so [Price] is accessible.
Have you made a summary field on the relationship to roll up the totals of the Sub table materials up to the Parent Invoice?