Course Number Generated by User input in two previous fields

Sean_Lake1 · ‎Feb 17, 2021

Hey all,
I have been testing out online DB’s, Knack, e.g., and created a formula to take three fields from inside the table and it generates a complete course number. Each of those fields were connected to their own tables.
The formula wound up being a text formula is:
{ILT Course Course Level.Course Level Number}{ILT Course Course Types.Course Code}.{Sequence Number}

How can I do this in AT? I’ve seen the formula option for a field, and even the Link to another Field(tab/table) option so it seems it’s possible, but when I attempt it I don’t get exactly what I was expecting. I get NaNa, or something like that LOL.

Any ideas?

Sean_Lake1 · ‎Feb 19, 2021

So here’s more.
I have the fields:

What’s the best way to handle this?
the {course level} & {course type} “.” {sequence number} Doesn’t deliver e.g. 203.02, as hoped, in fact I get an error.
The user selects the course level in the form as a single select format, while the course type is a link to field, and the sequence is also a single select(only because it has to have a 0 before it if it’s a single digit and then after 09, it goes to 10 to 99. Be great to fix that as well, but for now that’ll work.
Is there a way to make the formula use the number that’s tied to each of those fields?

Any ideas?

Sean_Lake1 · ‎Feb 20, 2021

Here’s the formula I have with, not exactly the results I want/need.
CONCATENATE({Course Level},{Course Types},".",{Sequence Number})

If I can somehow get the course level to display the number, at least, as well as the course type, then I believe the number structure would build properly.
In another DB software, it gave the option when doing a lookup, connected table, to choose the number option for the formula instead of just the text.

Justin_Barrett · ‎Feb 20, 2021

Welcome to the community, @Sean_Lake1! :grinning_face_with_big_eyes: The CONCATENATE() function that you found will do the trick when combining various items, but a more compact way is to use the & operator:

{Course Level} & {Course Types} & "." & {Sequence Number}

This is definitely possible. With your current setup, you could use a lookup field to retrieve the course code assigned to the linked course type, although in the screenshot you shared, the course code is in the primary field. Did you change the table setup after posting that shot?

As for the course level, your earlier screenshot also showed numbers in the {Course Level} field options, but your most recent screenshot just shows the name. Assigning a number is doable in either case, but the process will be different. If the number is in the single-select option, you can pull it like this:

LEFT({Course Level}, 1)

If the number is not there, it could be done like this:

SWITCH(
  {Course Level},
  "Intermediate", 1,
  "Introduction", 2,
  "Advanced", 3,
  "Master Studies", 4
)

Assuming that you’re able to look up the course code, the final formula might look like this:

SWITCH(
  {Course Level},
  "Intermediate", 1,
  "Introduction", 2,
  "Advanced", 3,
  "Master Studies", 4
) & {Course Code Lookup} & "." & {Sequence Number}

Does that help?

Sean_Lake1 · ‎Feb 20, 2021

Ok, so here’s the setup now. I did manage to use the CONCATENATE function, as you saw. I added a number to the Course Level:

The Course Type, is the tricky one. I guess with the Link to- automatically pulls the data from the second field. Yes I did make the numbers the unique ID as they do need to be unique. I wanted it to be autogenerated so that each time someone creates a new course type, it automatically added the two digit number. Right now, I’m happy with quick and dirty :).
So I’m guessing I could use a Lookup table then in the main table/tab and use THAT field to lookup the value instead of the text as it is now?

Does that make sense?
Ultimately, the end result I’m going for is:
Course Level & Course Type(course code) “.” sequence number

this is what I get from the course lookup

So I’m assuming that it’s not displaying the course code properly as that the “number” field is the unique id? Not sure, but I even tried doing, as you see course code, course type, etc It didn’t deliver exactly what I’d expected.

Sean_Lake1 · ‎Feb 20, 2021

Now I’m getting weird results after updating my course type table:

It’s displaying numbers, but not even the correct numbers(linked fields within 19, 20, 21???, bottom far right)

Almost there, right? LOL Unfortunately, the lookup fields still won’t give me the correct numbers :frowning:

Sean_Lake1 · ‎Feb 20, 2021

I think I broke it. I can’t get that danged number to display!

Notice the blank field(the lookup)???

Justin_Barrett · ‎Feb 20, 2021

Link fields are supposed to show the contents of the primary (first) field. Yours showing something else is definitely not the norm. Could you show the setup for that {Course Type} link field? Something seems fishy…

Sean_Lake1 · ‎Feb 20, 2021

Does this help, Justin?
I have a tendency to break things pretty easily.
CONCATENATE(LEFT({Course Level},1),{Course Codefrom Course Type},".",{Sequence Number})

Also, I think I broke my equation, it actually worked before, just that the numbers, as you saw weren’t correct LOL. SOOO CLOSE!

Sean_Lake1 · ‎Feb 20, 2021

Now I think I have it! Mwuhahaha:
CONCATENATE(LEFT({Course Level},1),{Course Type},".",{Sequence Number})