Obtain the value of a column in a reference table

[Robert Hames]

I have three custom tables that have a reference field in them that points to the next table. For example, Table C references Table B, and Table B references Table A.

What I need to have happen is that when the user tries to add a new row in Table C, the app should check the status of the corresponding row in Table A. If the status does not match certain statuses, then they should not be allowed to add the record.

My first thought was to use List Control on Table C and Omit the New button if the Omit new condition script was true. I tried this, and tried dot-walking up to the status in Table A, but I couldn't get this working.

As a "work-around", I wrote a Business Rule that fires on the submit of Table C, and it uses code to go up through the referenced rows, and obtain the status of Table A. This works, but the downside is that the user has to key in a new Table C row, and click on Submit. 

What is "best practice" for this scenario, and how do I get it to work?

Thanks!

[Chuck Tomasi]

I hope I caught all this... if someone is trying to add something to table C, how do you know what is corresponding in table A when C hasn't been created yet?

If you've already got an answer to that, why not use a client script/GlideAjax to do a quick lookup to see if it's applicable/available to add that row to C?

Some details are still vague so it's hard to give a firm answer on this. Some screenshots might be helpful.

[Robert Hames]

Okay, I'll try to clear up the confusion. Table A is a project table, and Table B is a project line table which has a reference field to the project table. Table C is a project work log table which has a reference field to the project line table.

The project table can have one of many statuses, such as "Started", "in-Progress", "Completed", etc. A user cannot add a work log row unless the project is in the correct state, i.e. has the correct status such as "in-Progress".

The reason I didn't do a client script is that it is triggered by onLoad, onSubmit, etc. and I need to stop them if the status is inappropriate. I could do a client script, and then make an AJAX call to see if the status on the Project table is appropriate, but if it isn't, how do I stop an onSubmit from happening?

The reason I went with the business rule is that it is triggered by insert or update, and I can abort by setting the abort action to true.

I wish I could provide screenshots, but my client is such that I can't. 

Thanks for your help, and any suggestions you may have would be welcomed as I feel like I'm still learning best practices.

[Chuck Tomasi]

Sounds like a great use case for a reference qualifier. No script needed. you should be able to dot-walk to the project status field and add a condition something like:

Project Line.Project.Status | is not | Completed

That filters out any project that is Completed and only gives them choices in the reference picker of Started or In progress.