I couldn't find an answer to this anywhere on the forum, probably because there is a simple solution I just can't see.
I am building a simple chemical inventory app. The general information is held in the Chemicals table. Each Chemical can have may Lots, which are in another table. The Lots table contains specific information about the individual container (lot number, exp. date, qty., etc.). The work flow for entering a new chemical requires data entry in the Chemicals Form. Once the Done button is clicked, I would like to redirect to the newly created chemical's Chemical Details page. The Chemical Details page has an Add Lot button to go to the Lots Form so the specific lot information can be added. Once the Done button is clicked, I would like to go back to the Chemical Details page.
My problem is, I can't seem to pass the newly created chemical to the Chemicals Detail page. I have tried
ThisRow() as well as the DataCell that contains the input of the new chemical but I get "VALUE" in the
InputRow of the Chemical Details page.
This is the Chemicals form.
This is the Chemicals Detail form.
This is the Lots Form.
Thanks in advance for the help.
I was able to come up with a work around for this, though it is not as elegant as I would like. I created an additional Chemicals Detail page and Lots Form page. Below I will reference them as NEW. It works like this.
If a new chemical is added via the Add Chemical button on my home page, the Chemical Form page opens and allows the general info to be added. Once the data is added, the Done button direct to the NEW Chemicals Detail page. The initial value of the $InputRow is set to
=FINDLASTROW(Chemicals,"ISTEXT(Chemicals[Chemical])=%",TRUE), which grabs the newly created chemical information. Once the Add Lot button is pushed it directs to the NEW Lots Form page, using the
=$InputRow to write the value to the NEW Lots Form. Once the lot information is put in, the Done button direct to the NEW Chemicals Detail, which again defaults to the most recently added chemical, so additional lots can be added if needed.
If a new lot needs to be added to an existing chemical. The original Chemicals Detail page is access by clicking on the chemical on the home page. From there the original Lots form can be accessed. Once the lot information has been added, the Done button is pushed, which directs to the previous page.
The flaw in this configuration is the possibility of another new chemical being added by another user while lots are being added by the first user. The LASTROW() function will cause the NEW Chemicals Detail page to reference the wrong chemical. This is highly unlikely with a small group of users, but could become problematic in a large group.
Anyone have a better way to handle this?
Thanks in advance!
@JCoop I think maybe you're super close, if in your first image $[chemical] is formatted as text.
Try writing that rowlink returned by your formula as the value for $[input row]
Why? =$[Chemical] is referencing the contents of the data cell $[chemical]. If that cell is text, then when you hit Done, the automation is looking inside that cell, seeing the word "Hydrogen" or whatever, and then passing that word (not any row) to the detail page. Instead, have it go get the last row and set that as the input row.