I am working on a business process workflow which sources data from several tables. All tables have relationships of varying types. In one of my forms there is an operator initials field which has it's data (eg SVG) sourced from the operator name columns. This data is then passed by a business rule to a field (called reference) on the form (eg SVG).
What I need to do is append the operator initials to a case number which comes from a different table (eg 4005676-SVG) to make the case reference. I have created a view which includes the case number through a table relationship. The problem is:-
a) Whilst I can add the case number column to the view I cannot include it in the form as it comes form a different entity/table (wish Microsoft would learn to name objects once and stick to it).
b) I cannot find any way of embedding the view in a form component so that it is updateable.
c) business rules appear to be ineffectual in this situation because of the single data source constraint.
d) I cannot find any way of using fx formulas in this instance. (there may be a way of creating a custom component that does the job. Any advice on that option would be appreciated)
e) The reference field (4005676-SVG) must be on a form so that it can be over writeable if the default operator (SVG) assigned to the case is not available and the case is being progressed by a stand in operator.
This is a fairly common situation in professional services office workflows. In a Windows or web application using SQL I would simply create an updateable view or temporary table or a method in a control or a constructor to deal with this situation. Unfortunately as far as I can tell Web Pages forms are not really equipped to handle this because of the single data source constraint and views being restricted to read only lists.
I would appreciate any suggestions on how this issue could be resolved in Web Pages forms if at all.
