How to filter selection in dynamic choice field according to selection in another choice field.
HI,
I have three tables EMPLOYEES, SHOPS, WORKING HOURS.
Then I have one form WORKING HOURS FORM.
Table Shops have field SHOP. Here is kept list of name of the shops.
Table Employees have fields NAME, PLACE OF WORK (dynamic field connected to table Shops). Here are recorded employees and in which shop they work.
In the WORKING HOUR FORM i have dynamic field "LOCATION" (connected to table Shops) and dynamic field "NAME". In the dynamic field name I need to filter names according to selection in the field Location. I have used this in dynamic values:
let y := LOCATION;
select EMPLOYEES where 'PLACE OF WORK' = y
At one point I did something and it worked, but then it stopped and I can't figure out why.
5 replies
-
Can you post a sample DB?
-
said:
instead of recoding names in the WORKING HOURS table it records numbers in fields EMPLOYEE and SHOP.Is there a reason why EMPLOYEE and SHOP are not reference fields? If they were reference fields then it will link the records correctly. This way all related WORKING HOURS records are linked to each EMPLOYEE and SHOP.
If you want to keep them simple text fields then just add text() around:
newRec.( SHOP := text(t.LOCATION); EMPLOYEE := text(t.NAME);
Why? LOCATION and NAME are dynamic choice fields. So you need to specifically tell Ninox which part of the data you want (choice id or choice value).
-
Hi,
I have tried to make it again, but still does not make record of NAME and SHOP if I add relationship between tables. Any chance to give some simple explanation how it works? I have tried to watch some videos and read some things, asked chatGP but does not give sense to me. I have attached ned database can you please point out what I did wrong and how set up properly relations so it enters the data?
Why? LOCATION and NAME are dynamic choice fields. So you need to specifically tell Ninox which part of the data you want (choice id or choice value).
I don't know what other field to use as this was close to what understood from info I found. I have very limited understanding of working with database so often go with solutions for which I found information which does not often means is good solution.
-
said:
I have tried to make it again, but still does not make record of NAME and SHOP if I add relationship between tables.It is confusing at first. Learning any new language is. Hang in there it will all come together the more you use it.
Thanks for the DB. It makes it very easy to troubleshoot.
The original code is:
let newRec := (create 'WORKING HOURS'); newRec.( SHOPS.(SHOPS := t.SHOP); EMPLOYEES.(NAME := t.NAME); 'WORK START' := t.'WORK START'; 'WORK END' := t.'WORK END' );
Imagine that you have manually created a new record in the WORKING HOURS table, what do you see. A record with no information. So your reference field SHOPS and EMPLOYEES are blank.
Now you want to link them to the selection made in the dynamic fields SHOP and NAME in WORKING HOURS FORM.
The trick behind reference fields is that you need to think record Id not about field data. I would recommend that you try to change your approach to your data. Instead of thinking how can I get the name of the employee. You should always be thinking how can I get the correct record from the Employee table to get me the Employee name.
This took me a while to understand and fully understand myself so give yourself time to get it.
Back to your code. The great thing about dynamic fields is that ones built on tables (since now you can use JSON as well) will give you the record Id. With that information we can link any reference field to the appropriate record.
You have already created a variable "t" that is equal to this. Great first step. Now you just need to change SHOPS and EMPLOYEES to be equal to the number of the selection made in the appropriate dynamic fields. Why? Because you only want the recordId of the selection not the text value of the selection. You see DERBY and NICOLE in the dynamic fields, but you need to keep in mind that it is the recordID that you want.
newRec.( SHOPS := number(t.SHOP); EMPLOYEES := number(t.NAME); 'WORK START' := t.'WORK START'; 'WORK END' := t.'WORK END' );
and you get this in the WORKING HOURS table:
I hope this helps.
Content aside
- 3 days agoLast active
- 5Replies
- 48Views
-
2
Following