Combining two tables in one view

[Pratyay_Rakshit]

Here’s another special demand from someone.

There’s a customer table with customer name, phone and other details.

There’s a sales table which links customers to some sales data, like a representative and an appointment date.

The specific request is to search for a phone number with in the form of the sales table, if its there then it should prefill all other customer fields (which is easily achievable using dereference), but if its not, then there should be an option to fill out that customer data with that particular form view, but the customer data should be saved in the customers table rather than the sales table (whose form we are using).

Is there any possible way to achieve this?

[Aleksi]

Do you have a Ref field in your sales table pointing to Customers table?

[Pratyay_Rakshit]

Yes, as of now.

[Aleksi]

When you have the Ref field, you are able to add a new Customer if it’s not present. Or are you looking for something else?

[Pratyay_Rakshit]

Yes, but that would navigate to the customers form.

What I am instructed to implement is the ability to write in all the new customer credentials within the same form view, i.e. the form of the sales table. But that would save the customers data within the customer table

[Aleksi]

You could trigger an Event action when he form is saved as “add a new row to another table using values from this row”. Though it sounds little weird to have the same data in two different records in two different tables.

[Pratyay_Rakshit]

Aleksi:

You could trigger an Event action when he form is saved as “add a new row to another table using values from this row”. Though it sounds little weird to have the same data in two different records in two different tables.

Yes, and I guess the action can be defined as to trigger only when the record is new?

[Aleksi]

Yes for example NOT(IN([KeyColumn],TableName[KeyColumn]))

[Grant_Stead]

I often create meta columns with a concatenation of various important information from that record. Then when I am referencing a table that has that meta column, then I go ahead and Dref that meta column. You can also go ahead and create an action that goes ahead and triggers all of the children of a parent to recalc the child drefs when the order changes…

I’m this way your can transfer metadata from one record to another record for searching and other purposes. Effectively you can also make it a comma separated list, and even put placeholders for fields that are blank. And then you can always index and extract specific values on demand

[Pratyay_Rakshit]

By meta columns do you mean virtual columns?

And can you simplify your explanation a bit? Its quite a bit for me to grasp all at once.