I want to make a two lists that makes one populate another

[wqwi853]

I'm trying to make a known offender reporting system. Basically what it does is someone goes in a creates a record of a known offender on one list. This would include things like name, address, known accomplices, etc.

Then I want to have a reporting form/list so records can be put against that offender's database record.

Two databases, two lists.

When someone goes into the "reporting" form/list to create a new incident on a known offender, I want a dropdown that the user can select the offenders record in the other table and prepopulate all the fields in the reporting form.

On top of that, I would like to make a report that when you look up a particular known offender, all the "reports" that were entered on the reporting form/list show up as a sort of history record for that particular person.

Is this possible with fabrik?

 

[cheesegrits]

Yes.

-- hugh

 

[wqwi853]

Okay, thanks.

I have one list for the actual offender records themselves. I also have a list for complaints against that offender. Do I use a join or do I use the PHP eval to populate the dropdown on the complaint list to grab all the offender records?

 

[wqwi853]

Well, is there any good documentation on how to do either a database join or the PHP eval option with examples?

 

[wqwi853]

Okay, I am closer to what I want to do. I used php eval to populate my drop down by modifying the example code provided.

My only issue now is that I need the drop down to contain two different columns in the other table such as "first_name" and "last_name".

I can only get the dropdown element to populate with either column but not both at the same time.

Is there a way to concatenate the two columns so they can both populate into the dropdown?

 

[wqwi853]

I am able to get the dataset I am looking for using the SQL statement "SELECT CONCAT(last_name,', ',first_name) AS fullname FROM offender_list"

When I put that into the php eval in my dropdown element I get...
"1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

 

[wqwi853]

lol, one sided conversation here but replacing the single quotes with double quotes fixed the problem.

 

[cheesegrits]

Not sure why you are using a dropdown with eval. Should be using a join element.

And yes, Community threads tend to be one sided, as I only have a very limited amount of time I can devote to free support.

-- hugh

 

[cheesegrits]

And in the join element, you would use the "CONCAT label" to create your full name, by giving the arguments you would use in your own CONCAT().

-- hugh

 

[wqwi853]

That's fine, I fully understand this is the community forum and I don't expect a rapid response. I just wanted to keep the thread updated as to how I was finally able to accomplish what I was looking for.

I tried doing a join with the dropdown element, I didn't even realize there was an actual join element... I'm still learning. I love this software.

In either case, I was able to accomplish populating my dropdown with two separate fields from a separate list's table and format them in a way that was satisfactory to me.