Single Dynamic field that populates other fields
I am replacing some fields that had quirky lookups with a single dynamic field.
I have a JobDockets table (related to Customers) ie N:1 - Customers can have many Jobdockets
In Jobdockets Table. I have a single dynamic contact field EContact (correctly working) showing the contact list for the selected customer.
What I want to do now is show by Formula the email address that is related to the specific contact that has been chosen in the dynamic drop down list. But I cannot get the script right.
in testing the formula with Customers.Contacts.Email shows that I am picking all up the emails relating to the specific customer contact is correct.
What I what to do is only show the only the email for the selected contact
let i:= number(EContact);
first(select Customers.Contacts where number(id)= i ( blah blah blah - cant get this bit right 
)
The last bit is where i am having brain fade !!!!!
and also not sure i i should call via customers to get at the contacts or call contacts direct
or should build a direct relationship from Jobdockets to Contacts?
7 replies
-
Have you considered using the record() command? I don't know if EContact pulls from the Customer table (it looks like it does), but if it does you already have the appropriate recordId.
Try:
let i:= record(Customer,number(eContact)); i.Contacts.EmailIf you want to go the other way, then the where goes at the Customer level:
let i:= number(EContact); first(((select Customers) where Id = i).Contacts.Email) -
Hi Fred
oh cheers i will give these a go
Econtact pulls from Contacts table which itself is a child of customers
-
Hey Fred
The first one goes in and is accepted but it returns an email that is not associated with the customer contact
The relationships flow is this
- Table : Customers
- Sub Child Table : Contacts
- Field on Contacts table: Full Name
- Field on Contacts table: Email
- Table: Job Dockets
- Dynamic Field on table EContact pulls data from line 3 above 'Full Name'
- Formula Field field : it is this that needs to pull in the email text from the field of the contact that is chosen in step 2 on the dynamic drop down
email in screen shot should be should be mel@118printdesign.co.uk not shazard@empires etc
Your suggested option 2 - I think needs to point either through Customers to get to Contacts or direct but i can't make it work ....
-
said:
The first one goes in and is accepted but it returns an email that is not associated with the customer contactYes, well I was wrong about the root of EContact. You just need to change the table name to match the root of EContact.
let i:= record(Contacts,number(eContact)); i.EmailWhich means the second bit of code you would change the select to Contacts instead of Customer.
-
Wow - Works buddy !
Top man ! Thank you sir
Not done much with Ninox dynamic links to date (as did stuff prior to the releasing and saw no need to change what was in place until now
Still have my head in the past with Microsoft DLookup and building ad hock relationship to do lookup Form1.Form2 field with Dataease
Still you don;t know what you don't know . and I can use what you just solved fro me moving forward.
I just need to monitor the performance on this lookup tho... verses have a sub table to select the contact!
CHEERS !
-
said:
I just need to monitor the performance on this lookup tho... verses have a sub table to select the contact!You made a good start by using the reference field to create the dynamic field instead of a select.
-
yes Fred. Weeding out as many selects as I can. As db currently has some 860,000 records in main table. it is worth doing ! where I can鈥檛 then I limit the amount of data I pull into views etc
-
Content aside
- Status Answered
- 6 mths agoLast active
- 7Replies
- 43Views
-
2
Following